Q (haiku): What is the median age at diagnosis for osteosarcoma patients in the TARGET study? ▶ read_guide { "uri": "cbioportal://clinical-data-guide" } ▶ search_oncotree { "search_term": "osteosarcoma" } ◀ result # Clinical Data Query Guide ## Overview Clinical data in cBioPortal is stored at both patient and sample levels. Understanding the distinction is crucial for accurate analysis. ## Data Organization ### Patient-Level vs Sample-Level Data - **Patient-level**: Demographics, overall survival, disease stage (stored once per patient) - **Sample-level**: Sample type, sequencing platform, purity (can have multiple per patient) ### Key Tables - `clinical_patient`: Patient-level clinical attributes - `clinical_sample`: Sample-level clinical attributes - `clinical_data_derived`: Pre-joined view combining both levels - `clinical_attribute_meta`: Metadata about available clinical attributes ## Recommended Approach: Use clinical_data_derived The `clinical_data_derived` table is pre-joined and optimized for most queries: ```sql -- Get clinical data for specific attributes SELECT sample_unique_id, patient_unique_id, attribute_name, attribute_value FROM clinical_data_derived WHERE cancer_study_identifier = 'your_study_id' AND attribute_name IN ('CANCER_TYPE', 'SAMPLE_TYPE', 'TMB_NONSYNONYMOUS'); ``` ## Clinical Attribute Discovery ### Use clinical_attribute_meta for Discovering Available Attributes **Always start here** to see what clinical attributes are available for a specific study: ```sql -- Discover available clinical attributes for a study SELECT attr_id, description, patient_attribute, cancer_study_id FROM clinical_attribute_meta WHERE cancer_study_id = ( SELECT cancer_study_id FROM cancer_study WHERE cancer_study_identifier = 'your_study_id' ) ORDER BY patient_attribute, attr_id; ``` **Key Fields:** - **attr_id**: matches attr_id in clinical_sample/clinical_patient tables - **description**: human-readable description of the attribute - **patient_attribute**: true = patient attribute, false = sample attribute - **cancer_study_id**: links to cancer_study table (filter by study) ## Attribute Semantics and Matching ### Case-Insensitive Matching for Attribute Values Clinical values are free text across studies and may differ only by case. For example, a controlled-looking value such as germline mutation status may appear as `GERMLINE`, `Germline`, or another case variant. When filtering `clinical_data_derived.attribute_value`, use case-insensitive matching unless you have already profiled the exact values in the target study: ```sql -- Correct: case-insensitive clinical value filter SELECT DISTINCT sample_unique_id, patient_unique_id FROM clinical_data_derived WHERE cancer_study_identifier = 'your_study_id' AND attribute_name = 'MUTATION_STATUS' AND upper(attribute_value) = 'GERMLINE'; ``` Do not write `attribute_value = 'GERMLINE'` without first checking all distinct values for that attribute in the study. ### Query the Requested Attribute, Not a Proxy Do not infer one clinical attribute from a related subtype or marker. Query the actual requested attribute when it … (17073 more chars) ◀ result {"result":[{"code":"OS","name":"Osteosarcoma","score":80,"mainType":"Bone Cancer","tissue":"Bone","hierarchy":"BONE > OS"},{"code":"CHOS","name":"Chondroblastic Osteosarcoma","score":50,"mainType":"Bone Cancer","tissue":"Bone","hierarchy":"BONE > OS > CHOS"},{"code":"FIOS","name":"Fibroblastic Osteosarcoma","score":50,"mainType":"Bone Cancer","tissue":"Bone","hierarchy":"BONE > OS > FIOS"},{"code":"HGSOS","name":"High-Grade Surface Osteosarcoma","score":50,"mainType":"Bone Cancer","tissue":"Bone","hierarchy":"BONE > OS > HGSOS"},{"code":"LGCOS","name":"Low-Grade Central Osteosarcoma","score":50,"mainType":"Bone Cancer","tissue":"Bone","hierarchy":"BONE > OS > LGCOS"},{"code":"OSOS","name":"Osteoblastic Osteosarcoma","score":50,"mainType":"Bone Cancer","tissue":"Bone","hierarchy":"BONE > OS > OSOS"},{"code":"PAOS","name":"Parosteal Osteosarcoma","score":50,"mainType":"Bone Cancer","tissue":"Bone","hierarchy":"BONE > OS > PAOS"},{"code":"PEOS","name":"Periosteal Osteosarcoma","score":50,"mainType":"Bone Cancer","tissue":"Bone","hierarchy":"BONE > OS > PEOS"},{"code":"SCOS","name":"Small Cell Osteosarcoma","score":50,"mainType":"Bone Cancer","tissue":"Bone","hierarchy":"BONE > OS > SCOS"},{"code":"SECOS","name":"Secondary Osteosarcoma","score":50,"mainType":"Bone Cancer","tissue":"Bone","hierarchy":"BONE > OS > SECOS"},{"code":"TEOS","name":"Telangiectatic Osteosarcoma","score":50,"mainType":"Bone Cancer","tissue":"Bone","hierarchy":"BONE > OS > TEOS"}]} ▶ list_studies { "search": "TARGET osteosarcoma", "limit": 10, "verbose": true } ◀ result {"result":[{"cancer_study_identifier":"os_target_gdc","name":"Osteosarcoma (TARGET GDC, 2025)","description":"TARGET Osteosarcoma. Source data from NCI GDC and generated in Aug 2025 using Cancer Data Aggregator.","type_of_cancer_id":"os","sample_count":159,"has_guide":true,"url":"https://www.cbioportal.org/study/summary?id=os_target_gdc"}]} ▶ clickhouse_list_tables {} ◀ result {"tables":[{"name":"allele_specific_copy_number"},{"name":"alteration_driver_annotation"},{"name":"cancer_study"},{"name":"cancer_study_query_preferences"},{"name":"cancer_study_tags"},{"name":"cbioportal_sequence_state"},{"name":"clinical_attribute_counts"},{"name":"clinical_attribute_meta"},{"name":"clinical_data_derived"},{"name":"clinical_event"},{"name":"clinical_event_data"},{"name":"clinical_event_data_derived"},{"name":"clinical_event_derived"},{"name":"clinical_patient"},{"name":"clinical_sample"},{"name":"cna_event"},{"name":"cna_panel_gene_coverage"},{"name":"cna_wes_coverage"},{"name":"co_altered_genes_in_study"},{"name":"copy_number_seg"},{"name":"copy_number_seg_file"},{"name":"gene"},{"name":"gene_alias"},{"name":"gene_alteration_frequency_by_cancer_type"},{"name":"gene_cna_distribution_in_study"},{"name":"gene_mutation_frequency_by_cancer_type"},{"name":"gene_mutation_frequency_in_studies"},{"name":"gene_mutation_frequency_in_study"},{"name":"gene_mutation_variants_in_study"},{"name":"gene_pair_coexpression"},{"name":"gene_panel"},{"name":"gene_panel_list"},{"name":"gene_panel_to_gene_derived"},{"name":"generic_assay_data_derived"},{"name":"generic_assay_meta_derived"},{"name":"generic_assay_profile_entity_derived"},{"name":"generic_entity_properties"},{"name":"geneset"},{"name":"geneset_gene"},{"name":"geneset_hierarchy_leaf"},{"name":"geneset_hierarchy_node"},{"name":"genetic_alteration"},{"name":"genetic_alteration_derived"},{"name":"genetic_entity"},{"name":"genetic_profile"},{"name":"genetic_profile_link"},{"name":"genetic_profile_samples"},{"name":"genomic_event_derived"},{"name":"gistic"},{"name":"gistic_to_gene"},{"name":"info"},{"name":"mut_sig"},{"name":"mutation"},{"name":"mutation_count_by_keyword"},{"name":"mutation_derived"},{"name":"mutation_event"},{"name":"mutation_panel_gene_coverage"},{"name":"mutation_wes_coverage"},{"name":"patient"},{"name":"reference_genome"},{"name":"reference_genome_gene"},{"name":"resource_definition"},{"name":"resource_patient"},{"name":"resource_sample"},{"name":"resource_study"},{"name":"sample"},{"name":"sample_cna_event"},{"name":"sample_derived"},{"name":"sample_list"},{"name":"sample_list_list"},{"name":"sample_profile"},{"name":"sample_to_gene_panel_derived"},{"name":"structural_variant"},{"name":"sv_panel_gene_coverage"},{"name":"sv_wes_coverage"},{"name":"top_cna_genes_in_study"},{"name":"top_mutated_genes_in_cohort"},{"name":"top_mutated_genes_in_study"},{"name":"top_sv_genes_in_study"},{"name":"treatment_counts_in_study"},{"name":"treatment_regimens_in_study"},{"name":"type_of_cancer"}]} ▶ clickhouse_run_select_query { "query": " SELECT DISTINCT attribute_name FROM clinical_data_derived WHERE cancer_study_identifier = 'os_target_gdc' AND (upper(attribute_name) LIKE '%AGE%' OR upper(attribute_name) LIKE '%BIRTH%' OR upper(attribute_name) LIKE '%DIAGNOSIS%') ORDER BY attribute_name LIMIT 50 " } ◀ result {"rows":[{"attribute_name":"AGE"},{"attribute_name":"DAYS_TO_BIRTH"},{"attribute_name":"PRIMARY_DIAGNOSIS"}]} ▶ clickhouse_run_select_query { "query": " WITH age_data AS ( SELECT DISTINCT patient_unique_id, toFloat64OrNull(attribute_value) AS days_to_birth FROM clinical_data_derived WHERE cancer_study_identifier = 'os_target_gdc' AND attribute_name = 'DAYS_TO_BIRTH' AND toFloat64OrNull(attribute_value) IS NOT NULL ) SELECT count() AS n_patients, round(quantile(0.5)(age_years), 1) AS median_age_years, round(avg(age_years), 1) AS mean_age_years, round(min(age_years), 1) AS min_age_years, round(max(age_years), 1) AS max_age_years FROM ( SELECT -days_to_birth / 365.25 AS age_years FROM age_data ) " } ◀ result {"rows":[{"n_patients":293,"median_age_years":15.2,"mean_age_years":16.2,"min_age_years":3.6,"max_age_years":87.1}]} ▶ resolve_and_route { "studyIds": [ "os_target_gdc" ] } ◀ result {"success":true,"message":"Found 1 study. Use the metadata to call the appropriate navigation tool(s).","data":{"totalCount":1,"studiesWithMetadata":[{"studyId":"os_target_gdc","name":"Osteosarcoma (TARGET GDC, 2025)","sampleCount":159,"studyViewUrl":"https://www.cbioportal.org/study?id=os_target_gdc","metadata":{"clinicalAttributeIds":["AGE","CANCER_TYPE","CANCER_TYPE_DETAILED","DAYS_TO_BIRTH","DAYS_TO_DEATH","DISEASE_TYPE","ETHNICITY","MUTATION_COUNT","ONCOTREE_CODE","OS_MONTHS","OS_STATUS","PRIMARY_DIAGNOSIS","PRIMARY_SITE_PATIENT","PROJECT_ID","RACE","SAMPLE_COUNT","SEX","SPECIMEN_TYPE","TMB_NONSYNONYMOUS","VITAL_STATUS"],"molecularProfileIds":["os_target_gdc_cna","os_target_gdc_mrna_seq_fpkm","os_target_gdc_mrna_seq_fpkm_Zscores","os_target_gdc_mrna_seq_read_counts_Zscores","os_target_gdc_mrna_seq_tpm","os_target_gdc_mrna_seq_tpm_Zscores","os_target_gdc_mutations","os_target_gdc_rna_seq_mrna"],"heatmapProfileIds":["os_target_gdc_mrna_seq_fpkm_Zscores","os_target_gdc_mrna_seq_read_counts_Zscores","os_target_gdc_mrna_seq_tpm_Zscores"],"availableComparisonTabs":["overlap","clinical","survival","alterations","mutations","mrna"]}}]}} ═ answer (success) ## Median Age at Diagnosis for TARGET Osteosarcoma Patients **Result:** The median age at diagnosis for osteosarcoma patients in the TARGET study is **15.2 years** (based on 293 patients with available age data). **Additional Age Statistics:** - **Mean age:** 16.2 years - **Range:** 3.6 – 87.1 years - **Sample size:** 293 patients with age data available **Technical Note:** Age was calculated from the `DAYS_TO_BIRTH` attribute (negative days from birth to diagnosis divided by 365.25 years), as the TARGET GDC study floors the raw `AGE` attribute at 18 years for de-identification purposes. --- [View TARGET Osteosarcoma Study](https://www.cbioportal.org/study?id=os_target_gdc)