Hello,
I have created a new post based off my previous post to give full context to my issue as I had oversimplified previously.
So I have an imputation method I need to apply to an ADQS dataset for a questionnaire called the EORTC QLQ-C30. The EORTC QLQ-C30 is a standardized questionnaire for assessing the quality of life in cancer patients. It measures various health-related aspects, including symptoms, functional capabilities, and overall well-being, across 30 items.
The imputation method is
" Handling of intercurrent events:
Death will be considered a treatment failure/deterioration in assessment of global health status and functional subscales of the EORTC QLQ-C30. Missing data (global health status and functional EORTC QLQ-C30 scores) recorded after the intercurrent event will be penalized, i.e., imputed/substituted by zeros (which is the lowest possible score), until the time point in which 10% of subjects are still on treatment."
So for this section " until the time point in which 10% of subjects are still on treatment." we have a list of AVISIT/AVISITN
data visits; input AVISIT :$20. AVISITN 8.; infile datalines dlm = '|'; datalines; Cycle 2 Day 1|201 Cycle 3 Day 1|301 Cycle 4 Day 1|401 Cycle 5 Day 1|501 Cycle 6 Day 1|601 Cycle 7 Day 1|701 Cycle 8 Day 1|801 Cycle 9 Day 1|901 Cycle 10 Day 1|1001 Cycle 11 Day 1|1101 Cycle 12 Day 1|1201 Cycle 13 Day 1|1301 Cycle 14 Day 1|1401 Cycle 15 Day 1|1501 Cycle 16 Day 1|1601 Cycle 17 Day 1|1701 Cycle 18 Day 1|1801 Cycle 19 Day 1|1901 Cycle 20 Day 1|2001 Cycle 21 Day 1|2101 Cycle 22 Day 1|2201 Cycle 23 Day 1|2301 Cycle 24 Day 1|2401 Cycle 25 Day 1|2501 Cycle 26 Day 1|2601 Cycle 27 Day 1|2701 Cycle 28 Day 1|2801 Cycle 29 Day 1|2901 Cycle 30 Day 1|3001 Cycle 31 Day 1|3101 Cycle 32 Day 1|3201 Cycle 33 Day 1|3301 Cycle 34 Day 1|3401 Cycle 35 Day 1|3501 Cycle 36 Day 1|3601 Cycle 37 Day 1|3701 Cycle 38 Day 1|3801 Cycle 39 Day 1|3901 End of Treatment|9001 40-Day Follow-up|9002 ; run;
and when I apply this code
/* Step 1: Create a flag for ADT > TRTEDT */ data flagged_data; set adqs; if index(upcase(AVISIT), 'CYCLE') > 0 then do; /* Checks if 'AVISIT' contains 'Cycle' */ if ADT <= TRTEDT then flag = 1; else flag = 0; output; /* Output only the records with 'AVISIT' containing 'Cycle' */ end; run; proc sql; create table cycle_summary as select distinct a.avisit, a.avisitn, a.ady, sum(a.flag) as num_meeting_condition, first_visit.total_assessments_at_first_visit as total_assessments_at_start, (sum(a.flag) / first_visit.total_assessments_at_first_visit) * 100 as proportion_of_first_visit, count(distinct a.usubjid) as subjects_at_visit, total_subjects.total_count as total_subjects_at_start, (count(distinct a.usubjid) / total_subjects.total_count) * 100 as percent_remaining from flagged_data a /* Join with subquery to get the total assessments at the first visit */ cross join (select count(*) as total_assessments_at_first_visit from flagged_data where avisitn = (select min(avisitn) from flagged_data)) as first_visit /* Join with subquery to get the total subjects at the start */ cross join (select count(distinct usubjid) as total_count from flagged_data) as total_subjects /* Ensure that the groups are ordered and filtered according to the percentage criterion */ group by a.avisit, a.avisitn having (count(distinct case when a.flag=1 then a.usubjid else " " end) / total_subjects.total_count) * 100 >= 10 order by a.avisitn desc; quit; /* New DATA step to apply formats */ data cycle_summary_formatted; set cycle_summary; format proportion_of_first_visit 8.2 percent_remaining 8.2; run; /* Step 3: Identify the last cycle with >= 10% */ proc sort data=cycle_summary_formatted out=cycle_summary_sorted; by descending avisitn; run; data _null_; set cycle_summary_sorted; where proportion_of_first_visit >= 10; if _N_ = 1 then do; call symputx('last_cycle_avisit', avisit); call symputx('last_cycle_avisitn', avisitn); end; run; /* Now, :last_cycle_avisit contains the last AVISIT and :last_cycle_avisitn contains the last AVISITN meeting the condition */ %put Last AVISIT: &last_cycle_avisit; %put Last AVISITN: &last_cycle_avisitn;
and we get a value of Cycle 26 Day 1/2601 for AVISIT/AVISITN.
For this section "Missing data (global health status and functional EORTC QLQ-C30 scores) recorded after the intercurrent event will be penalized, i.e., imputed/substituted by zeros (which is the lowest possible score)" Global health status and functional EORTC QLQ-C30 scores are all records where PARCAT2 = 'GOLBAL HEALTH STATUS' or 'FUNCTIONAL SCALES'. So if I ran this code
proc sql;
create table distinct_values as
select distinct param, paramcd
from qs00
where upcase(parcat2) in ('GLOBAL HEALTH STATUS', 'FUNCTIONAL SCALES');
quit;
I would get this data
data distinct_values; input PARAM :$30. PARAMCD $3.; infile datalines dlm = '|'; datalines; EORTC QLQ-C30 Cognitive Functioning|CF EORTC QLQ-C30 Emotional Functioning|EF EORTC QLQ-C30 Global Health Status|QL2 EORTC QLQ-C30 Physical Functioning|PF2 EORTC QLQ-C30 Role Functioning|RF2 EORTC QLQ-C30 Social Functioning|SF ; run;
So the below have and want datasets are a sample of what I need for every subsection of param/paramcd where upcase(parcat2) in ('GLOBAL HEALTH STATUS', 'FUNCTIONAL SCALES');
up to &last_cycle_avisitn where DTHFL='Y'. for subjects that didn't die I need to keep as is and ultimately have a dataset containing all records with the imputed records added that satisy this criteria
data have; input USUBJID $ TRTSDT :date9. TRTEDT :date9. DTHFL $ AVISIT :$20. AVISITN :8. ADT :date9. PARAM :$40. PARAMCD :$5. PARCAT2 :$30. AVAL :8. BASE :8. CHG :8.; format TRTSDT TRTEDT ADT date9.; infile datalines dlm = '|'; datalines; 1001|17DEC2021|16SEP2022|Y|Cycle 2 Day 1|201|07JAN2022|EORTC QLQ-C30 Cognitive Functioning|CF|Functional Scales|83.333333333|83.333333333|0| 1001|17DEC2021|16SEP2022|Y|Cycle 3 Day 1|301|27JAN2022|EORTC QLQ-C30 Cognitive Functioning|CF|Functional Scales|50|83.333333333|-33.33333333| 1001|17DEC2021|16SEP2022|Y|Cycle 4 Day 1|401|18FEB2022|EORTC QLQ-C30 Cognitive Functioning|CF|Functional Scales|50|83.333333333|-33.33333333| 1001|17DEC2021|16SEP2022|Y|Cycle 5 Day 1|501|11MAR2022|EORTC QLQ-C30 Cognitive Functioning|CF|Functional Scales|66.666666667|83.333333333|-16.66666667| 1001|17DEC2021|16SEP2022|Y|Cycle 6 Day 1|601|01APR2022|EORTC QLQ-C30 Cognitive Functioning|CF|Functional Scales|66.666666667|83.333333333|-16.66666667| 1001|17DEC2021|16SEP2022|Y|Cycle 7 Day 1|701|21APR2022|EORTC QLQ-C30 Cognitive Functioning|CF|Functional Scales|66.666666667|83.333333333|-16.66666667| 1001|17DEC2021|16SEP2022|Y|Cycle 8 Day 1|801|13MAY2022|EORTC QLQ-C30 Cognitive Functioning|CF|Functional Scales|83.333333333|83.333333333|0| 1001|17DEC2021|16SEP2022|Y|Cycle 9 Day 1|901|02JUN2022|EORTC QLQ-C30 Cognitive Functioning|CF|Functional Scales|66.666666667|83.333333333|-16.66666667| 1001|17DEC2021|16SEP2022|Y|Cycle 10 Day 1|1001|24JUN2022|EORTC QLQ-C30 Cognitive Functioning|CF|Functional Scales|66.666666667|83.333333333|-16.66666667| 1001|17DEC2021|16SEP2022|Y|Cycle 11 Day 1|1101|14JUL2022|EORTC QLQ-C30 Cognitive Functioning|CF|Functional Scales|66.666666667|83.333333333|-16.66666667| 1001|17DEC2021|16SEP2022|Y|Cycle 12 Day 1|1201|05AUG2022|EORTC QLQ-C30 Cognitive Functioning|CF|Functional Scales|66.666666667|83.333333333|-16.66666667| 1001|17DEC2021|16SEP2022|Y|Cycle 13 Day 1|1301|25AUG2022|EORTC QLQ-C30 Cognitive Functioning|CF|Functional Scales|66.666666667|83.333333333|-16.66666667| 1001|17DEC2021|16SEP2022|Y|Cycle 14 Day 1|1401|16SEP2022|EORTC QLQ-C30 Cognitive Functioning|CF|Functional Scales|66.666666667|83.333333333|-16.66666667| 1001|17DEC2021|16SEP2022|Y|Cycle 2 Day 1|201|07JAN2022|EORTC QLQ-C30 Emotional Functioning|EF|Functional Scales|91.666666667|75|16.666666667| 1001|17DEC2021|16SEP2022|Y|Cycle 3 Day 1|301|27JAN2022|EORTC QLQ-C30 Emotional Functioning|EF|Functional Scales|75|75|0| 1001|17DEC2021|16SEP2022|Y|Cycle 4 Day 1|401|18FEB2022|EORTC QLQ-C30 Emotional Functioning|EF|Functional Scales|66.666666667|75|-8.333333333| 1001|17DEC2021|16SEP2022|Y|Cycle 5 Day 1|501|11MAR2022|EORTC QLQ-C30 Emotional Functioning|EF|Functional Scales|83.333333333|75|8.3333333333| 1001|17DEC2021|16SEP2022|Y|Cycle 6 Day 1|601|01APR2022|EORTC QLQ-C30 Emotional Functioning|EF|Functional Scales|83.333333333|75|8.3333333333| 1001|17DEC2021|16SEP2022|Y|Cycle 7 Day 1|701|21APR2022|EORTC QLQ-C30 Emotional Functioning|EF|Functional Scales|75|75|0| 1001|17DEC2021|16SEP2022|Y|Cycle 8 Day 1|801|13MAY2022|EORTC QLQ-C30 Emotional Functioning|EF|Functional Scales|75|75|0| 1001|17DEC2021|16SEP2022|Y|Cycle 9 Day 1|901|02JUN2022|EORTC QLQ-C30 Emotional Functioning|EF|Functional Scales|75|75|0| 1001|17DEC2021|16SEP2022|Y|Cycle 10 Day 1|1001|24JUN2022|EORTC QLQ-C30 Emotional Functioning|EF|Functional Scales|83.333333333|75|8.3333333333| 1001|17DEC2021|16SEP2022|Y|Cycle 11 Day 1|1101|14JUL2022|EORTC QLQ-C30 Emotional Functioning|EF|Functional Scales|83.333333333|75|8.3333333333| 1001|17DEC2021|16SEP2022|Y|Cycle 12 Day 1|1201|05AUG2022|EORTC QLQ-C30 Emotional Functioning|EF|Functional Scales|58.333333333|75|-16.66666667| 1001|17DEC2021|16SEP2022|Y|Cycle 13 Day 1|1301|25AUG2022|EORTC QLQ-C30 Emotional Functioning|EF|Functional Scales|83.333333333|75|8.3333333333| 1001|17DEC2021|16SEP2022|Y|Cycle 14 Day 1|1401|16SEP2022|EORTC QLQ-C30 Emotional Functioning|EF|Functional Scales|75|75|0| 1001|17DEC2021|16SEP2022|Y|Cycle 2 Day 1|201|07JAN2022|EORTC QLQ-C30 Physical Functioning|PF2|Functional Scales|66.666666667|60|6.6666666667| 1001|17DEC2021|16SEP2022|Y|Cycle 3 Day 1|301|27JAN2022|EORTC QLQ-C30 Physical Functioning|PF2|Functional Scales|73.333333333|60|13.333333333| 1001|17DEC2021|16SEP2022|Y|Cycle 4 Day 1|401|18FEB2022|EORTC QLQ-C30 Physical Functioning|PF2|Functional Scales|80|60|20| 1001|17DEC2021|16SEP2022|Y|Cycle 5 Day 1|501|11MAR2022|EORTC QLQ-C30 Physical Functioning|PF2|Functional Scales|66.666666667|60|6.6666666667| 1001|17DEC2021|16SEP2022|Y|Cycle 6 Day 1|601|01APR2022|EORTC QLQ-C30 Physical Functioning|PF2|Functional Scales|66.666666667|60|6.6666666667| 1001|17DEC2021|16SEP2022|Y|Cycle 7 Day 1|701|21APR2022|EORTC QLQ-C30 Physical Functioning|PF2|Functional Scales|60|60|0| 1001|17DEC2021|16SEP2022|Y|Cycle 8 Day 1|801|13MAY2022|EORTC QLQ-C30 Physical Functioning|PF2|Functional Scales|80|60|20| 1001|17DEC2021|16SEP2022|Y|Cycle 9 Day 1|901|02JUN2022|EORTC QLQ-C30 Physical Functioning|PF2|Functional Scales|60|60|0| 1001|17DEC2021|16SEP2022|Y|Cycle 10 Day 1|1001|24JUN2022|EORTC QLQ-C30 Physical Functioning|PF2|Functional Scales|60|60|0| 1001|17DEC2021|16SEP2022|Y|Cycle 11 Day 1|1101|14JUL2022|EORTC QLQ-C30 Physical Functioning|PF2|Functional Scales|60|60|0| 1001|17DEC2021|16SEP2022|Y|Cycle 12 Day 1|1201|05AUG2022|EORTC QLQ-C30 Physical Functioning|PF2|Functional Scales|60|60|0| 1001|17DEC2021|16SEP2022|Y|Cycle 13 Day 1|1301|25AUG2022|EORTC QLQ-C30 Physical Functioning|PF2|Functional Scales|66.666666667|60|6.6666666667| 1001|17DEC2021|16SEP2022|Y|Cycle 14 Day 1|1401|16SEP2022|EORTC QLQ-C30 Physical Functioning|PF2|Functional Scales|66.666666667|60|6.6666666667| 1001|17DEC2021|16SEP2022|Y|Cycle 2 Day 1|201|07JAN2022|EORTC QLQ-C30 Global Health Status|QL2|Global Health Status|75|25|50| 1001|17DEC2021|16SEP2022|Y|Cycle 3 Day 1|301|27JAN2022|EORTC QLQ-C30 Global Health Status|QL2|Global Health Status|66.666666667|25|41.666666667| 1001|17DEC2021|16SEP2022|Y|Cycle 4 Day 1|401|18FEB2022|EORTC QLQ-C30 Global Health Status|QL2|Global Health Status|58.333333333|25|33.333333333| 1001|17DEC2021|16SEP2022|Y|Cycle 5 Day 1|501|11MAR2022|EORTC QLQ-C30 Global Health Status|QL2|Global Health Status|41.666666667|25|16.666666667| 1001|17DEC2021|16SEP2022|Y|Cycle 6 Day 1|601|01APR2022|EORTC QLQ-C30 Global Health Status|QL2|Global Health Status|50|25|25| 1001|17DEC2021|16SEP2022|Y|Cycle 7 Day 1|701|21APR2022|EORTC QLQ-C30 Global Health Status|QL2|Global Health Status|50|25|25| 1001|17DEC2021|16SEP2022|Y|Cycle 8 Day 1|801|13MAY2022|EORTC QLQ-C30 Global Health Status|QL2|Global Health Status|66.666666667|25|41.666666667| 1001|17DEC2021|16SEP2022|Y|Cycle 9 Day 1|901|02JUN2022|EORTC QLQ-C30 Global Health Status|QL2|Global Health Status|58.333333333|25|33.333333333| 1001|17DEC2021|16SEP2022|Y|Cycle 10 Day 1|1001|24JUN2022|EORTC QLQ-C30 Global Health Status|QL2|Global Health Status|58.333333333|25|33.333333333| 1001|17DEC2021|16SEP2022|Y|Cycle 11 Day 1|1101|14JUL2022|EORTC QLQ-C30 Global Health Status|QL2|Global Health Status|33.333333333|25|8.3333333333| 1001|17DEC2021|16SEP2022|Y|Cycle 12 Day 1|1201|05AUG2022|EORTC QLQ-C30 Global Health Status|QL2|Global Health Status|50|25|25| 1001|17DEC2021|16SEP2022|Y|Cycle 13 Day 1|1301|25AUG2022|EORTC QLQ-C30 Global Health Status|QL2|Global Health Status|66.666666667|25|41.666666667| 1001|17DEC2021|16SEP2022|Y|Cycle 14 Day 1|1401|16SEP2022|EORTC QLQ-C30 Global Health Status|QL2|Global Health Status|50|25|25| ; run; data want; input USUBJID $ TRTSDT :date9. TRTEDT :date9. DTHFL $ AVISIT :$20. AVISITN :8. ADT :date9. PARAM :$40. PARAMCD :$5. PARCAT2 :$30. AVAL :8. BASE :8. CHG :8.; format TRTSDT TRTEDT ADT date9.; infile datalines dlm = '|'; datalines; 1001|17DEC2021|16SEP2022|Y|Cycle 2 Day 1|201|07JAN2022|EORTC QLQ-C30 Cognitive Functioning|CF|Functional Scales|83.333333333|83.333333333|0| 1001|17DEC2021|16SEP2022|Y|Cycle 3 Day 1|301|27JAN2022|EORTC QLQ-C30 Cognitive Functioning|CF|Functional Scales|50|83.333333333|-33.33333333| 1001|17DEC2021|16SEP2022|Y|Cycle 4 Day 1|401|18FEB2022|EORTC QLQ-C30 Cognitive Functioning|CF|Functional Scales|50|83.333333333|-33.33333333| 1001|17DEC2021|16SEP2022|Y|Cycle 5 Day 1|501|11MAR2022|EORTC QLQ-C30 Cognitive Functioning|CF|Functional Scales|66.666666667|83.333333333|-16.66666667| 1001|17DEC2021|16SEP2022|Y|Cycle 6 Day 1|601|01APR2022|EORTC QLQ-C30 Cognitive Functioning|CF|Functional Scales|66.666666667|83.333333333|-16.66666667| 1001|17DEC2021|16SEP2022|Y|Cycle 7 Day 1|701|21APR2022|EORTC QLQ-C30 Cognitive Functioning|CF|Functional Scales|66.666666667|83.333333333|-16.66666667| 1001|17DEC2021|16SEP2022|Y|Cycle 8 Day 1|801|13MAY2022|EORTC QLQ-C30 Cognitive Functioning|CF|Functional Scales|83.333333333|83.333333333|0| 1001|17DEC2021|16SEP2022|Y|Cycle 9 Day 1|901|02JUN2022|EORTC QLQ-C30 Cognitive Functioning|CF|Functional Scales|66.666666667|83.333333333|-16.66666667| 1001|17DEC2021|16SEP2022|Y|Cycle 10 Day 1|1001|24JUN2022|EORTC QLQ-C30 Cognitive Functioning|CF|Functional Scales|66.666666667|83.333333333|-16.66666667| 1001|17DEC2021|16SEP2022|Y|Cycle 11 Day 1|1101|14JUL2022|EORTC QLQ-C30 Cognitive Functioning|CF|Functional Scales|66.666666667|83.333333333|-16.66666667| 1001|17DEC2021|16SEP2022|Y|Cycle 12 Day 1|1201|05AUG2022|EORTC QLQ-C30 Cognitive Functioning|CF|Functional Scales|66.666666667|83.333333333|-16.66666667| 1001|17DEC2021|16SEP2022|Y|Cycle 13 Day 1|1301|25AUG2022|EORTC QLQ-C30 Cognitive Functioning|CF|Functional Scales|66.666666667|83.333333333|-16.66666667| 1001|17DEC2021|16SEP2022|Y|Cycle 14 Day 1|1401|16SEP2022|EORTC QLQ-C30 Cognitive Functioning|CF|Functional Scales|66.666666667|83.333333333|-16.66666667| 1001|17DEC2021|16SEP2022|Y|Cycle 15 Day 1|1501| |EORTC QLQ-C30 Cognitive Functioning|CF|Functional Scales|0|83.333333333|-83.333333333| 1001|17DEC2021|16SEP2022|Y|Cycle 16 Day 1|1601| |EORTC QLQ-C30 Cognitive Functioning|CF|Functional Scales|0|83.333333333|-83.333333333| 1001|17DEC2021|16SEP2022|Y|Cycle 17 Day 1|1701| |EORTC QLQ-C30 Cognitive Functioning|CF|Functional Scales|0|83.333333333|-83.333333333| 1001|17DEC2021|16SEP2022|Y|Cycle 18 Day 1|1801| |EORTC QLQ-C30 Cognitive Functioning|CF|Functional Scales|0|83.333333333|-83.333333333| 1001|17DEC2021|16SEP2022|Y|Cycle 19 Day 1|1901| |EORTC QLQ-C30 Cognitive Functioning|CF|Functional Scales|0|83.333333333|-83.333333333| 1001|17DEC2021|16SEP2022|Y|Cycle 20 Day 1|2001| |EORTC QLQ-C30 Cognitive Functioning|CF|Functional Scales|0|83.333333333|-83.333333333| 1001|17DEC2021|16SEP2022|Y|Cycle 21 Day 1|2101| |EORTC QLQ-C30 Cognitive Functioning|CF|Functional Scales|0|83.333333333|-83.333333333| 1001|17DEC2021|16SEP2022|Y|Cycle 22 Day 1|2201| |EORTC QLQ-C30 Cognitive Functioning|CF|Functional Scales|0|83.333333333|-83.333333333| 1001|17DEC2021|16SEP2022|Y|Cycle 23 Day 1|2301| |EORTC QLQ-C30 Cognitive Functioning|CF|Functional Scales|0|83.333333333|-83.333333333| 1001|17DEC2021|16SEP2022|Y|Cycle 24 Day 1|2401| |EORTC QLQ-C30 Cognitive Functioning|CF|Functional Scales|0|83.333333333|-83.333333333| 1001|17DEC2021|16SEP2022|Y|Cycle 25 Day 1|2501| |EORTC QLQ-C30 Cognitive Functioning|CF|Functional Scales|0|83.333333333|-83.333333333| 1001|17DEC2021|16SEP2022|Y|Cycle 26 Day 1|2601| |EORTC QLQ-C30 Cognitive Functioning|CF|Functional Scales|0|83.333333333|-83.333333333| 1001|17DEC2021|16SEP2022|Y|Cycle 2 Day 1|201|07JAN2022|EORTC QLQ-C30 Emotional Functioning|EF|Functional Scales|91.666666667|75|16.666666667| 1001|17DEC2021|16SEP2022|Y|Cycle 3 Day 1|301|27JAN2022|EORTC QLQ-C30 Emotional Functioning|EF|Functional Scales|75|75|0| 1001|17DEC2021|16SEP2022|Y|Cycle 4 Day 1|401|18FEB2022|EORTC QLQ-C30 Emotional Functioning|EF|Functional Scales|66.666666667|75|-8.333333333| 1001|17DEC2021|16SEP2022|Y|Cycle 5 Day 1|501|11MAR2022|EORTC QLQ-C30 Emotional Functioning|EF|Functional Scales|83.333333333|75|8.3333333333| 1001|17DEC2021|16SEP2022|Y|Cycle 6 Day 1|601|01APR2022|EORTC QLQ-C30 Emotional Functioning|EF|Functional Scales|83.333333333|75|8.3333333333| 1001|17DEC2021|16SEP2022|Y|Cycle 7 Day 1|701|21APR2022|EORTC QLQ-C30 Emotional Functioning|EF|Functional Scales|75|75|0| 1001|17DEC2021|16SEP2022|Y|Cycle 8 Day 1|801|13MAY2022|EORTC QLQ-C30 Emotional Functioning|EF|Functional Scales|75|75|0| 1001|17DEC2021|16SEP2022|Y|Cycle 9 Day 1|901|02JUN2022|EORTC QLQ-C30 Emotional Functioning|EF|Functional Scales|75|75|0| 1001|17DEC2021|16SEP2022|Y|Cycle 10 Day 1|1001|24JUN2022|EORTC QLQ-C30 Emotional Functioning|EF|Functional Scales|83.333333333|75|8.3333333333| 1001|17DEC2021|16SEP2022|Y|Cycle 11 Day 1|1101|14JUL2022|EORTC QLQ-C30 Emotional Functioning|EF|Functional Scales|83.333333333|75|8.3333333333| 1001|17DEC2021|16SEP2022|Y|Cycle 12 Day 1|1201|05AUG2022|EORTC QLQ-C30 Emotional Functioning|EF|Functional Scales|58.333333333|75|-16.66666667| 1001|17DEC2021|16SEP2022|Y|Cycle 13 Day 1|1301|25AUG2022|EORTC QLQ-C30 Emotional Functioning|EF|Functional Scales|83.333333333|75|8.3333333333| 1001|17DEC2021|16SEP2022|Y|Cycle 14 Day 1|1401|16SEP2022|EORTC QLQ-C30 Emotional Functioning|EF|Functional Scales|75|75|0| 1001|17DEC2021|16SEP2022|Y|Cycle 15 Day 1|1501| |EORTC QLQ-C30 Emotional Functioning|EF|Functional Scales|0|75|-75| 1001|17DEC2021|16SEP2022|Y|Cycle 16 Day 1|1601| |EORTC QLQ-C30 Emotional Functioning|EF|Functional Scales|0|75|-75| 1001|17DEC2021|16SEP2022|Y|Cycle 17 Day 1|1701| |EORTC QLQ-C30 Emotional Functioning|EF|Functional Scales|0|75|-75| 1001|17DEC2021|16SEP2022|Y|Cycle 18 Day 1|1801| |EORTC QLQ-C30 Emotional Functioning|EF|Functional Scales|0|75|-75| 1001|17DEC2021|16SEP2022|Y|Cycle 19 Day 1|1901| |EORTC QLQ-C30 Emotional Functioning|EF|Functional Scales|0|75|-75| 1001|17DEC2021|16SEP2022|Y|Cycle 20 Day 1|2001| |EORTC QLQ-C30 Emotional Functioning|EF|Functional Scales|0|75|-75| 1001|17DEC2021|16SEP2022|Y|Cycle 21 Day 1|2101| |EORTC QLQ-C30 Emotional Functioning|EF|Functional Scales|0|75|-75| 1001|17DEC2021|16SEP2022|Y|Cycle 22 Day 1|2201| |EORTC QLQ-C30 Emotional Functioning|EF|Functional Scales|0|75|-75| 1001|17DEC2021|16SEP2022|Y|Cycle 23 Day 1|2301| |EORTC QLQ-C30 Emotional Functioning|EF|Functional Scales|0|75|-75| 1001|17DEC2021|16SEP2022|Y|Cycle 24 Day 1|2401| |EORTC QLQ-C30 Emotional Functioning|EF|Functional Scales|0|75|-75| 1001|17DEC2021|16SEP2022|Y|Cycle 25 Day 1|2501| |EORTC QLQ-C30 Emotional Functioning|EF|Functional Scales|0|75|-75| 1001|17DEC2021|16SEP2022|Y|Cycle 26 Day 1|2601| |EORTC QLQ-C30 Emotional Functioning|EF|Functional Scales|0|75|-75| 1001|17DEC2021|16SEP2022|Y|Cycle 2 Day 1|201|07JAN2022|EORTC QLQ-C30 Physical Functioning|PF2|Functional Scales|66.666666667|60|6.6666666667| 1001|17DEC2021|16SEP2022|Y|Cycle 3 Day 1|301|27JAN2022|EORTC QLQ-C30 Physical Functioning|PF2|Functional Scales|73.333333333|60|13.333333333| 1001|17DEC2021|16SEP2022|Y|Cycle 4 Day 1|401|18FEB2022|EORTC QLQ-C30 Physical Functioning|PF2|Functional Scales|80|60|20| 1001|17DEC2021|16SEP2022|Y|Cycle 5 Day 1|501|11MAR2022|EORTC QLQ-C30 Physical Functioning|PF2|Functional Scales|66.666666667|60|6.6666666667| 1001|17DEC2021|16SEP2022|Y|Cycle 6 Day 1|601|01APR2022|EORTC QLQ-C30 Physical Functioning|PF2|Functional Scales|66.666666667|60|6.6666666667| 1001|17DEC2021|16SEP2022|Y|Cycle 7 Day 1|701|21APR2022|EORTC QLQ-C30 Physical Functioning|PF2|Functional Scales|60|60|0| 1001|17DEC2021|16SEP2022|Y|Cycle 8 Day 1|801|13MAY2022|EORTC QLQ-C30 Physical Functioning|PF2|Functional Scales|80|60|20| 1001|17DEC2021|16SEP2022|Y|Cycle 9 Day 1|901|02JUN2022|EORTC QLQ-C30 Physical Functioning|PF2|Functional Scales|60|60|0| 1001|17DEC2021|16SEP2022|Y|Cycle 10 Day 1|1001|24JUN2022|EORTC QLQ-C30 Physical Functioning|PF2|Functional Scales|60|60|0| 1001|17DEC2021|16SEP2022|Y|Cycle 11 Day 1|1101|14JUL2022|EORTC QLQ-C30 Physical Functioning|PF2|Functional Scales|60|60|0| 1001|17DEC2021|16SEP2022|Y|Cycle 12 Day 1|1201|05AUG2022|EORTC QLQ-C30 Physical Functioning|PF2|Functional Scales|60|60|0| 1001|17DEC2021|16SEP2022|Y|Cycle 13 Day 1|1301|25AUG2022|EORTC QLQ-C30 Physical Functioning|PF2|Functional Scales|66.666666667|60|6.6666666667| 1001|17DEC2021|16SEP2022|Y|Cycle 14 Day 1|1401|16SEP2022|EORTC QLQ-C30 Physical Functioning|PF2|Functional Scales|66.666666667|60|6.6666666667| 1001|17DEC2021|16SEP2022|Y|Cycle 15 Day 1|1501| |EORTC QLQ-C30 Physical Functioning|PF2|Functional Scales|0|60|-60| 1001|17DEC2021|16SEP2022|Y|Cycle 16 Day 1|1601| |EORTC QLQ-C30 Physical Functioning|PF2|Functional Scales|0|60|-60| 1001|17DEC2021|16SEP2022|Y|Cycle 17 Day 1|1701| |EORTC QLQ-C30 Physical Functioning|PF2|Functional Scales|0|60|-60| 1001|17DEC2021|16SEP2022|Y|Cycle 18 Day 1|1801| |EORTC QLQ-C30 Physical Functioning|PF2|Functional Scales|0|60|-60| 1001|17DEC2021|16SEP2022|Y|Cycle 19 Day 1|1901| |EORTC QLQ-C30 Physical Functioning|PF2|Functional Scales|0|60|-60| 1001|17DEC2021|16SEP2022|Y|Cycle 20 Day 1|2001| |EORTC QLQ-C30 Physical Functioning|PF2|Functional Scales|0|60|-60| 1001|17DEC2021|16SEP2022|Y|Cycle 21 Day 1|2101| |EORTC QLQ-C30 Physical Functioning|PF2|Functional Scales|0|60|-60| 1001|17DEC2021|16SEP2022|Y|Cycle 22 Day 1|2201| |EORTC QLQ-C30 Physical Functioning|PF2|Functional Scales|0|60|-60| 1001|17DEC2021|16SEP2022|Y|Cycle 23 Day 1|2301| |EORTC QLQ-C30 Physical Functioning|PF2|Functional Scales|0|60|-60| 1001|17DEC2021|16SEP2022|Y|Cycle 24 Day 1|2401| |EORTC QLQ-C30 Physical Functioning|PF2|Functional Scales|0|60|-60| 1001|17DEC2021|16SEP2022|Y|Cycle 25 Day 1|2501| |EORTC QLQ-C30 Physical Functioning|PF2|Functional Scales|0|60|-60| 1001|17DEC2021|16SEP2022|Y|Cycle 26 Day 1|2601| |EORTC QLQ-C30 Physical Functioning|PF2|Functional Scales|0|60|-60| 1001|17DEC2021|16SEP2022|Y|Cycle 2 Day 1|201|07JAN2022|EORTC QLQ-C30 Global Health Status|QL2|Global Health Status|75|25|50| 1001|17DEC2021|16SEP2022|Y|Cycle 3 Day 1|301|27JAN2022|EORTC QLQ-C30 Global Health Status|QL2|Global Health Status|66.666666667|25|41.666666667| 1001|17DEC2021|16SEP2022|Y|Cycle 4 Day 1|401|18FEB2022|EORTC QLQ-C30 Global Health Status|QL2|Global Health Status|58.333333333|25|33.333333333| 1001|17DEC2021|16SEP2022|Y|Cycle 5 Day 1|501|11MAR2022|EORTC QLQ-C30 Global Health Status|QL2|Global Health Status|41.666666667|25|16.666666667| 1001|17DEC2021|16SEP2022|Y|Cycle 6 Day 1|601|01APR2022|EORTC QLQ-C30 Global Health Status|QL2|Global Health Status|50|25|25| 1001|17DEC2021|16SEP2022|Y|Cycle 7 Day 1|701|21APR2022|EORTC QLQ-C30 Global Health Status|QL2|Global Health Status|50|25|25| 1001|17DEC2021|16SEP2022|Y|Cycle 8 Day 1|801|13MAY2022|EORTC QLQ-C30 Global Health Status|QL2|Global Health Status|66.666666667|25|41.666666667| 1001|17DEC2021|16SEP2022|Y|Cycle 9 Day 1|901|02JUN2022|EORTC QLQ-C30 Global Health Status|QL2|Global Health Status|58.333333333|25|33.333333333| 1001|17DEC2021|16SEP2022|Y|Cycle 10 Day 1|1001|24JUN2022|EORTC QLQ-C30 Global Health Status|QL2|Global Health Status|58.333333333|25|33.333333333| 1001|17DEC2021|16SEP2022|Y|Cycle 11 Day 1|1101|14JUL2022|EORTC QLQ-C30 Global Health Status|QL2|Global Health Status|33.333333333|25|8.3333333333| 1001|17DEC2021|16SEP2022|Y|Cycle 12 Day 1|1201|05AUG2022|EORTC QLQ-C30 Global Health Status|QL2|Global Health Status|50|25|25| 1001|17DEC2021|16SEP2022|Y|Cycle 13 Day 1|1301|25AUG2022|EORTC QLQ-C30 Global Health Status|QL2|Global Health Status|66.666666667|25|41.666666667| 1001|17DEC2021|16SEP2022|Y|Cycle 14 Day 1|1401|16SEP2022|EORTC QLQ-C30 Global Health Status|QL2|Global Health Status|50|25|25| 1001|17DEC2021|16SEP2022|Y|Cycle 15 Day 1|1501| |EORTC QLQ-C30 Global Health Status|QL2|Global Health Status|0|25|-25| 1001|17DEC2021|16SEP2022|Y|Cycle 16 Day 1|1601| |EORTC QLQ-C30 Global Health Status|QL2|Global Health Status|0|25|-25| 1001|17DEC2021|16SEP2022|Y|Cycle 17 Day 1|1701| |EORTC QLQ-C30 Global Health Status|QL2|Global Health Status|0|25|-25| 1001|17DEC2021|16SEP2022|Y|Cycle 18 Day 1|1801| |EORTC QLQ-C30 Global Health Status|QL2|Global Health Status|0|25|-25| 1001|17DEC2021|16SEP2022|Y|Cycle 19 Day 1|1901| |EORTC QLQ-C30 Global Health Status|QL2|Global Health Status|0|25|-25| 1001|17DEC2021|16SEP2022|Y|Cycle 20 Day 1|2001| |EORTC QLQ-C30 Global Health Status|QL2|Global Health Status|0|25|-25| 1001|17DEC2021|16SEP2022|Y|Cycle 21 Day 1|2101| |EORTC QLQ-C30 Global Health Status|QL2|Global Health Status|0|25|-25| 1001|17DEC2021|16SEP2022|Y|Cycle 22 Day 1|2201| |EORTC QLQ-C30 Global Health Status|QL2|Global Health Status|0|25|-25| 1001|17DEC2021|16SEP2022|Y|Cycle 23 Day 1|2301| |EORTC QLQ-C30 Global Health Status|QL2|Global Health Status|0|25|-25| 1001|17DEC2021|16SEP2022|Y|Cycle 24 Day 1|2401| |EORTC QLQ-C30 Global Health Status|QL2|Global Health Status|0|25|-25| 1001|17DEC2021|16SEP2022|Y|Cycle 25 Day 1|2501| |EORTC QLQ-C30 Global Health Status|QL2|Global Health Status|0|25|-25| 1001|17DEC2021|16SEP2022|Y|Cycle 26 Day 1|2601| |EORTC QLQ-C30 Global Health Status|QL2|Global Health Status|0|25|-25| ; run;
like that:
data visits;
input AVISIT :$20. AVISITN 8.;
infile datalines dlm = '|';
datalines;
Cycle 2 Day 1|201
Cycle 3 Day 1|301
Cycle 4 Day 1|401
Cycle 5 Day 1|501
Cycle 6 Day 1|601
Cycle 7 Day 1|701
Cycle 8 Day 1|801
Cycle 9 Day 1|901
Cycle 10 Day 1|1001
Cycle 11 Day 1|1101
Cycle 12 Day 1|1201
Cycle 13 Day 1|1301
Cycle 14 Day 1|1401
Cycle 15 Day 1|1501
Cycle 16 Day 1|1601
Cycle 17 Day 1|1701
Cycle 18 Day 1|1801
Cycle 19 Day 1|1901
Cycle 20 Day 1|2001
Cycle 21 Day 1|2101
Cycle 22 Day 1|2201
Cycle 23 Day 1|2301
Cycle 24 Day 1|2401
Cycle 25 Day 1|2501
Cycle 26 Day 1|2601
Cycle 27 Day 1|2701
Cycle 28 Day 1|2801
Cycle 29 Day 1|2901
Cycle 30 Day 1|3001
Cycle 31 Day 1|3101
Cycle 32 Day 1|3201
Cycle 33 Day 1|3301
Cycle 34 Day 1|3401
Cycle 35 Day 1|3501
Cycle 36 Day 1|3601
Cycle 37 Day 1|3701
Cycle 38 Day 1|3801
Cycle 39 Day 1|3901
End of Treatment|9001
40-Day Follow-up|9002
;
run;
data have;
input USUBJID $ TRTSDT :date9. TRTEDT :date9. DTHFL $ AVISIT :$20. AVISITN :8. ADT :date9.
PARAM :$40. PARAMCD :$5. PARCAT2 :$30. AVAL :8. BASE :8. CHG :8.;
format TRTSDT TRTEDT ADT date9.;
infile datalines dlm = '|';
datalines;
1001|17DEC2021|16SEP2022|Y|Cycle 2 Day 1|201|07JAN2022|EORTC QLQ-C30 Cognitive Functioning|CF|Functional Scales|83.333333333|83.333333333|0|
1001|17DEC2021|16SEP2022|Y|Cycle 3 Day 1|301|27JAN2022|EORTC QLQ-C30 Cognitive Functioning|CF|Functional Scales|50|83.333333333|-33.33333333|
1001|17DEC2021|16SEP2022|Y|Cycle 4 Day 1|401|18FEB2022|EORTC QLQ-C30 Cognitive Functioning|CF|Functional Scales|50|83.333333333|-33.33333333|
1001|17DEC2021|16SEP2022|Y|Cycle 5 Day 1|501|11MAR2022|EORTC QLQ-C30 Cognitive Functioning|CF|Functional Scales|66.666666667|83.333333333|-16.66666667|
1001|17DEC2021|16SEP2022|Y|Cycle 6 Day 1|601|01APR2022|EORTC QLQ-C30 Cognitive Functioning|CF|Functional Scales|66.666666667|83.333333333|-16.66666667|
1001|17DEC2021|16SEP2022|Y|Cycle 7 Day 1|701|21APR2022|EORTC QLQ-C30 Cognitive Functioning|CF|Functional Scales|66.666666667|83.333333333|-16.66666667|
1001|17DEC2021|16SEP2022|Y|Cycle 8 Day 1|801|13MAY2022|EORTC QLQ-C30 Cognitive Functioning|CF|Functional Scales|83.333333333|83.333333333|0|
1001|17DEC2021|16SEP2022|Y|Cycle 9 Day 1|901|02JUN2022|EORTC QLQ-C30 Cognitive Functioning|CF|Functional Scales|66.666666667|83.333333333|-16.66666667|
1001|17DEC2021|16SEP2022|Y|Cycle 10 Day 1|1001|24JUN2022|EORTC QLQ-C30 Cognitive Functioning|CF|Functional Scales|66.666666667|83.333333333|-16.66666667|
1001|17DEC2021|16SEP2022|Y|Cycle 11 Day 1|1101|14JUL2022|EORTC QLQ-C30 Cognitive Functioning|CF|Functional Scales|66.666666667|83.333333333|-16.66666667|
1001|17DEC2021|16SEP2022|Y|Cycle 12 Day 1|1201|05AUG2022|EORTC QLQ-C30 Cognitive Functioning|CF|Functional Scales|66.666666667|83.333333333|-16.66666667|
1001|17DEC2021|16SEP2022|Y|Cycle 13 Day 1|1301|25AUG2022|EORTC QLQ-C30 Cognitive Functioning|CF|Functional Scales|66.666666667|83.333333333|-16.66666667|
1001|17DEC2021|16SEP2022|Y|Cycle 14 Day 1|1401|16SEP2022|EORTC QLQ-C30 Cognitive Functioning|CF|Functional Scales|66.666666667|83.333333333|-16.66666667|
1001|17DEC2021|16SEP2022|Y|Cycle 2 Day 1|201|07JAN2022|EORTC QLQ-C30 Emotional Functioning|EF|Functional Scales|91.666666667|75|16.666666667|
1001|17DEC2021|16SEP2022|Y|Cycle 3 Day 1|301|27JAN2022|EORTC QLQ-C30 Emotional Functioning|EF|Functional Scales|75|75|0|
1001|17DEC2021|16SEP2022|Y|Cycle 4 Day 1|401|18FEB2022|EORTC QLQ-C30 Emotional Functioning|EF|Functional Scales|66.666666667|75|-8.333333333|
1001|17DEC2021|16SEP2022|Y|Cycle 5 Day 1|501|11MAR2022|EORTC QLQ-C30 Emotional Functioning|EF|Functional Scales|83.333333333|75|8.3333333333|
1001|17DEC2021|16SEP2022|Y|Cycle 6 Day 1|601|01APR2022|EORTC QLQ-C30 Emotional Functioning|EF|Functional Scales|83.333333333|75|8.3333333333|
1001|17DEC2021|16SEP2022|Y|Cycle 7 Day 1|701|21APR2022|EORTC QLQ-C30 Emotional Functioning|EF|Functional Scales|75|75|0|
1001|17DEC2021|16SEP2022|Y|Cycle 8 Day 1|801|13MAY2022|EORTC QLQ-C30 Emotional Functioning|EF|Functional Scales|75|75|0|
1001|17DEC2021|16SEP2022|Y|Cycle 9 Day 1|901|02JUN2022|EORTC QLQ-C30 Emotional Functioning|EF|Functional Scales|75|75|0|
1001|17DEC2021|16SEP2022|Y|Cycle 10 Day 1|1001|24JUN2022|EORTC QLQ-C30 Emotional Functioning|EF|Functional Scales|83.333333333|75|8.3333333333|
1001|17DEC2021|16SEP2022|Y|Cycle 11 Day 1|1101|14JUL2022|EORTC QLQ-C30 Emotional Functioning|EF|Functional Scales|83.333333333|75|8.3333333333|
1001|17DEC2021|16SEP2022|Y|Cycle 12 Day 1|1201|05AUG2022|EORTC QLQ-C30 Emotional Functioning|EF|Functional Scales|58.333333333|75|-16.66666667|
1001|17DEC2021|16SEP2022|Y|Cycle 13 Day 1|1301|25AUG2022|EORTC QLQ-C30 Emotional Functioning|EF|Functional Scales|83.333333333|75|8.3333333333|
1001|17DEC2021|16SEP2022|Y|Cycle 14 Day 1|1401|16SEP2022|EORTC QLQ-C30 Emotional Functioning|EF|Functional Scales|75|75|0|
1001|17DEC2021|16SEP2022|Y|Cycle 2 Day 1|201|07JAN2022|EORTC QLQ-C30 Physical Functioning|PF2|Functional Scales|66.666666667|60|6.6666666667|
1001|17DEC2021|16SEP2022|Y|Cycle 3 Day 1|301|27JAN2022|EORTC QLQ-C30 Physical Functioning|PF2|Functional Scales|73.333333333|60|13.333333333|
1001|17DEC2021|16SEP2022|Y|Cycle 4 Day 1|401|18FEB2022|EORTC QLQ-C30 Physical Functioning|PF2|Functional Scales|80|60|20|
1001|17DEC2021|16SEP2022|Y|Cycle 5 Day 1|501|11MAR2022|EORTC QLQ-C30 Physical Functioning|PF2|Functional Scales|66.666666667|60|6.6666666667|
1001|17DEC2021|16SEP2022|Y|Cycle 6 Day 1|601|01APR2022|EORTC QLQ-C30 Physical Functioning|PF2|Functional Scales|66.666666667|60|6.6666666667|
1001|17DEC2021|16SEP2022|Y|Cycle 7 Day 1|701|21APR2022|EORTC QLQ-C30 Physical Functioning|PF2|Functional Scales|60|60|0|
1001|17DEC2021|16SEP2022|Y|Cycle 8 Day 1|801|13MAY2022|EORTC QLQ-C30 Physical Functioning|PF2|Functional Scales|80|60|20|
1001|17DEC2021|16SEP2022|Y|Cycle 9 Day 1|901|02JUN2022|EORTC QLQ-C30 Physical Functioning|PF2|Functional Scales|60|60|0|
1001|17DEC2021|16SEP2022|Y|Cycle 10 Day 1|1001|24JUN2022|EORTC QLQ-C30 Physical Functioning|PF2|Functional Scales|60|60|0|
1001|17DEC2021|16SEP2022|Y|Cycle 11 Day 1|1101|14JUL2022|EORTC QLQ-C30 Physical Functioning|PF2|Functional Scales|60|60|0|
1001|17DEC2021|16SEP2022|Y|Cycle 12 Day 1|1201|05AUG2022|EORTC QLQ-C30 Physical Functioning|PF2|Functional Scales|60|60|0|
1001|17DEC2021|16SEP2022|Y|Cycle 13 Day 1|1301|25AUG2022|EORTC QLQ-C30 Physical Functioning|PF2|Functional Scales|66.666666667|60|6.6666666667|
1001|17DEC2021|16SEP2022|Y|Cycle 14 Day 1|1401|16SEP2022|EORTC QLQ-C30 Physical Functioning|PF2|Functional Scales|66.666666667|60|6.6666666667|
1001|17DEC2021|16SEP2022|Y|Cycle 2 Day 1|201|07JAN2022|EORTC QLQ-C30 Global Health Status|QL2|Global Health Status|75|25|50|
1001|17DEC2021|16SEP2022|Y|Cycle 3 Day 1|301|27JAN2022|EORTC QLQ-C30 Global Health Status|QL2|Global Health Status|66.666666667|25|41.666666667|
1001|17DEC2021|16SEP2022|Y|Cycle 4 Day 1|401|18FEB2022|EORTC QLQ-C30 Global Health Status|QL2|Global Health Status|58.333333333|25|33.333333333|
1001|17DEC2021|16SEP2022|Y|Cycle 5 Day 1|501|11MAR2022|EORTC QLQ-C30 Global Health Status|QL2|Global Health Status|41.666666667|25|16.666666667|
1001|17DEC2021|16SEP2022|Y|Cycle 6 Day 1|601|01APR2022|EORTC QLQ-C30 Global Health Status|QL2|Global Health Status|50|25|25|
1001|17DEC2021|16SEP2022|Y|Cycle 7 Day 1|701|21APR2022|EORTC QLQ-C30 Global Health Status|QL2|Global Health Status|50|25|25|
1001|17DEC2021|16SEP2022|Y|Cycle 8 Day 1|801|13MAY2022|EORTC QLQ-C30 Global Health Status|QL2|Global Health Status|66.666666667|25|41.666666667|
1001|17DEC2021|16SEP2022|Y|Cycle 9 Day 1|901|02JUN2022|EORTC QLQ-C30 Global Health Status|QL2|Global Health Status|58.333333333|25|33.333333333|
1001|17DEC2021|16SEP2022|Y|Cycle 10 Day 1|1001|24JUN2022|EORTC QLQ-C30 Global Health Status|QL2|Global Health Status|58.333333333|25|33.333333333|
1001|17DEC2021|16SEP2022|Y|Cycle 11 Day 1|1101|14JUL2022|EORTC QLQ-C30 Global Health Status|QL2|Global Health Status|33.333333333|25|8.3333333333|
1001|17DEC2021|16SEP2022|Y|Cycle 12 Day 1|1201|05AUG2022|EORTC QLQ-C30 Global Health Status|QL2|Global Health Status|50|25|25|
1001|17DEC2021|16SEP2022|Y|Cycle 13 Day 1|1301|25AUG2022|EORTC QLQ-C30 Global Health Status|QL2|Global Health Status|66.666666667|25|41.666666667|
1001|17DEC2021|16SEP2022|Y|Cycle 14 Day 1|1401|16SEP2022|EORTC QLQ-C30 Global Health Status|QL2|Global Health Status|50|25|25|
;
run;
options ls=max;
%let last_cycle_avisitn = 26;
data cycle_lookup;
set visits;
where AVISITN <= &last_cycle_avisitn.01;
run;
data want2;
/* array for mising visits */
array v[2:&last_cycle_avisitn.] _temporary_;
/* array for visits names */
array vn[2:&last_cycle_avisitn.] _temporary_;
array vc[2:&last_cycle_avisitn.] $ 20 _temporary_;
if _N_=1 then do until(EOF1);
set cycle_lookup end=EOF1;
vn[round(AVISITN/100,1)]=AVISITN;
vc[round(AVISITN/100,1)]=AVISIT;
put _all_;
end;
set have;
by USUBJID PARAMCD;
if first.PARAMCD then call missing(of v[*]);
v[round(AVISITN/100,1)] = 1;
output;
if last.PARAMCD then
do _N_ = 2 to &last_cycle_avisitn.;
if ^v[_N_] then
do;
AVISITN=vn[_N_];
AVISIT =vc[_N_];
ADT =.;
AVAL=0;
output;
end;
end;
run;
proc print data = want2;
run;
?
Bart
like that:
data visits;
input AVISIT :$20. AVISITN 8.;
infile datalines dlm = '|';
datalines;
Cycle 2 Day 1|201
Cycle 3 Day 1|301
Cycle 4 Day 1|401
Cycle 5 Day 1|501
Cycle 6 Day 1|601
Cycle 7 Day 1|701
Cycle 8 Day 1|801
Cycle 9 Day 1|901
Cycle 10 Day 1|1001
Cycle 11 Day 1|1101
Cycle 12 Day 1|1201
Cycle 13 Day 1|1301
Cycle 14 Day 1|1401
Cycle 15 Day 1|1501
Cycle 16 Day 1|1601
Cycle 17 Day 1|1701
Cycle 18 Day 1|1801
Cycle 19 Day 1|1901
Cycle 20 Day 1|2001
Cycle 21 Day 1|2101
Cycle 22 Day 1|2201
Cycle 23 Day 1|2301
Cycle 24 Day 1|2401
Cycle 25 Day 1|2501
Cycle 26 Day 1|2601
Cycle 27 Day 1|2701
Cycle 28 Day 1|2801
Cycle 29 Day 1|2901
Cycle 30 Day 1|3001
Cycle 31 Day 1|3101
Cycle 32 Day 1|3201
Cycle 33 Day 1|3301
Cycle 34 Day 1|3401
Cycle 35 Day 1|3501
Cycle 36 Day 1|3601
Cycle 37 Day 1|3701
Cycle 38 Day 1|3801
Cycle 39 Day 1|3901
End of Treatment|9001
40-Day Follow-up|9002
;
run;
data have;
input USUBJID $ TRTSDT :date9. TRTEDT :date9. DTHFL $ AVISIT :$20. AVISITN :8. ADT :date9.
PARAM :$40. PARAMCD :$5. PARCAT2 :$30. AVAL :8. BASE :8. CHG :8.;
format TRTSDT TRTEDT ADT date9.;
infile datalines dlm = '|';
datalines;
1001|17DEC2021|16SEP2022|Y|Cycle 2 Day 1|201|07JAN2022|EORTC QLQ-C30 Cognitive Functioning|CF|Functional Scales|83.333333333|83.333333333|0|
1001|17DEC2021|16SEP2022|Y|Cycle 3 Day 1|301|27JAN2022|EORTC QLQ-C30 Cognitive Functioning|CF|Functional Scales|50|83.333333333|-33.33333333|
1001|17DEC2021|16SEP2022|Y|Cycle 4 Day 1|401|18FEB2022|EORTC QLQ-C30 Cognitive Functioning|CF|Functional Scales|50|83.333333333|-33.33333333|
1001|17DEC2021|16SEP2022|Y|Cycle 5 Day 1|501|11MAR2022|EORTC QLQ-C30 Cognitive Functioning|CF|Functional Scales|66.666666667|83.333333333|-16.66666667|
1001|17DEC2021|16SEP2022|Y|Cycle 6 Day 1|601|01APR2022|EORTC QLQ-C30 Cognitive Functioning|CF|Functional Scales|66.666666667|83.333333333|-16.66666667|
1001|17DEC2021|16SEP2022|Y|Cycle 7 Day 1|701|21APR2022|EORTC QLQ-C30 Cognitive Functioning|CF|Functional Scales|66.666666667|83.333333333|-16.66666667|
1001|17DEC2021|16SEP2022|Y|Cycle 8 Day 1|801|13MAY2022|EORTC QLQ-C30 Cognitive Functioning|CF|Functional Scales|83.333333333|83.333333333|0|
1001|17DEC2021|16SEP2022|Y|Cycle 9 Day 1|901|02JUN2022|EORTC QLQ-C30 Cognitive Functioning|CF|Functional Scales|66.666666667|83.333333333|-16.66666667|
1001|17DEC2021|16SEP2022|Y|Cycle 10 Day 1|1001|24JUN2022|EORTC QLQ-C30 Cognitive Functioning|CF|Functional Scales|66.666666667|83.333333333|-16.66666667|
1001|17DEC2021|16SEP2022|Y|Cycle 11 Day 1|1101|14JUL2022|EORTC QLQ-C30 Cognitive Functioning|CF|Functional Scales|66.666666667|83.333333333|-16.66666667|
1001|17DEC2021|16SEP2022|Y|Cycle 12 Day 1|1201|05AUG2022|EORTC QLQ-C30 Cognitive Functioning|CF|Functional Scales|66.666666667|83.333333333|-16.66666667|
1001|17DEC2021|16SEP2022|Y|Cycle 13 Day 1|1301|25AUG2022|EORTC QLQ-C30 Cognitive Functioning|CF|Functional Scales|66.666666667|83.333333333|-16.66666667|
1001|17DEC2021|16SEP2022|Y|Cycle 14 Day 1|1401|16SEP2022|EORTC QLQ-C30 Cognitive Functioning|CF|Functional Scales|66.666666667|83.333333333|-16.66666667|
1001|17DEC2021|16SEP2022|Y|Cycle 2 Day 1|201|07JAN2022|EORTC QLQ-C30 Emotional Functioning|EF|Functional Scales|91.666666667|75|16.666666667|
1001|17DEC2021|16SEP2022|Y|Cycle 3 Day 1|301|27JAN2022|EORTC QLQ-C30 Emotional Functioning|EF|Functional Scales|75|75|0|
1001|17DEC2021|16SEP2022|Y|Cycle 4 Day 1|401|18FEB2022|EORTC QLQ-C30 Emotional Functioning|EF|Functional Scales|66.666666667|75|-8.333333333|
1001|17DEC2021|16SEP2022|Y|Cycle 5 Day 1|501|11MAR2022|EORTC QLQ-C30 Emotional Functioning|EF|Functional Scales|83.333333333|75|8.3333333333|
1001|17DEC2021|16SEP2022|Y|Cycle 6 Day 1|601|01APR2022|EORTC QLQ-C30 Emotional Functioning|EF|Functional Scales|83.333333333|75|8.3333333333|
1001|17DEC2021|16SEP2022|Y|Cycle 7 Day 1|701|21APR2022|EORTC QLQ-C30 Emotional Functioning|EF|Functional Scales|75|75|0|
1001|17DEC2021|16SEP2022|Y|Cycle 8 Day 1|801|13MAY2022|EORTC QLQ-C30 Emotional Functioning|EF|Functional Scales|75|75|0|
1001|17DEC2021|16SEP2022|Y|Cycle 9 Day 1|901|02JUN2022|EORTC QLQ-C30 Emotional Functioning|EF|Functional Scales|75|75|0|
1001|17DEC2021|16SEP2022|Y|Cycle 10 Day 1|1001|24JUN2022|EORTC QLQ-C30 Emotional Functioning|EF|Functional Scales|83.333333333|75|8.3333333333|
1001|17DEC2021|16SEP2022|Y|Cycle 11 Day 1|1101|14JUL2022|EORTC QLQ-C30 Emotional Functioning|EF|Functional Scales|83.333333333|75|8.3333333333|
1001|17DEC2021|16SEP2022|Y|Cycle 12 Day 1|1201|05AUG2022|EORTC QLQ-C30 Emotional Functioning|EF|Functional Scales|58.333333333|75|-16.66666667|
1001|17DEC2021|16SEP2022|Y|Cycle 13 Day 1|1301|25AUG2022|EORTC QLQ-C30 Emotional Functioning|EF|Functional Scales|83.333333333|75|8.3333333333|
1001|17DEC2021|16SEP2022|Y|Cycle 14 Day 1|1401|16SEP2022|EORTC QLQ-C30 Emotional Functioning|EF|Functional Scales|75|75|0|
1001|17DEC2021|16SEP2022|Y|Cycle 2 Day 1|201|07JAN2022|EORTC QLQ-C30 Physical Functioning|PF2|Functional Scales|66.666666667|60|6.6666666667|
1001|17DEC2021|16SEP2022|Y|Cycle 3 Day 1|301|27JAN2022|EORTC QLQ-C30 Physical Functioning|PF2|Functional Scales|73.333333333|60|13.333333333|
1001|17DEC2021|16SEP2022|Y|Cycle 4 Day 1|401|18FEB2022|EORTC QLQ-C30 Physical Functioning|PF2|Functional Scales|80|60|20|
1001|17DEC2021|16SEP2022|Y|Cycle 5 Day 1|501|11MAR2022|EORTC QLQ-C30 Physical Functioning|PF2|Functional Scales|66.666666667|60|6.6666666667|
1001|17DEC2021|16SEP2022|Y|Cycle 6 Day 1|601|01APR2022|EORTC QLQ-C30 Physical Functioning|PF2|Functional Scales|66.666666667|60|6.6666666667|
1001|17DEC2021|16SEP2022|Y|Cycle 7 Day 1|701|21APR2022|EORTC QLQ-C30 Physical Functioning|PF2|Functional Scales|60|60|0|
1001|17DEC2021|16SEP2022|Y|Cycle 8 Day 1|801|13MAY2022|EORTC QLQ-C30 Physical Functioning|PF2|Functional Scales|80|60|20|
1001|17DEC2021|16SEP2022|Y|Cycle 9 Day 1|901|02JUN2022|EORTC QLQ-C30 Physical Functioning|PF2|Functional Scales|60|60|0|
1001|17DEC2021|16SEP2022|Y|Cycle 10 Day 1|1001|24JUN2022|EORTC QLQ-C30 Physical Functioning|PF2|Functional Scales|60|60|0|
1001|17DEC2021|16SEP2022|Y|Cycle 11 Day 1|1101|14JUL2022|EORTC QLQ-C30 Physical Functioning|PF2|Functional Scales|60|60|0|
1001|17DEC2021|16SEP2022|Y|Cycle 12 Day 1|1201|05AUG2022|EORTC QLQ-C30 Physical Functioning|PF2|Functional Scales|60|60|0|
1001|17DEC2021|16SEP2022|Y|Cycle 13 Day 1|1301|25AUG2022|EORTC QLQ-C30 Physical Functioning|PF2|Functional Scales|66.666666667|60|6.6666666667|
1001|17DEC2021|16SEP2022|Y|Cycle 14 Day 1|1401|16SEP2022|EORTC QLQ-C30 Physical Functioning|PF2|Functional Scales|66.666666667|60|6.6666666667|
1001|17DEC2021|16SEP2022|Y|Cycle 2 Day 1|201|07JAN2022|EORTC QLQ-C30 Global Health Status|QL2|Global Health Status|75|25|50|
1001|17DEC2021|16SEP2022|Y|Cycle 3 Day 1|301|27JAN2022|EORTC QLQ-C30 Global Health Status|QL2|Global Health Status|66.666666667|25|41.666666667|
1001|17DEC2021|16SEP2022|Y|Cycle 4 Day 1|401|18FEB2022|EORTC QLQ-C30 Global Health Status|QL2|Global Health Status|58.333333333|25|33.333333333|
1001|17DEC2021|16SEP2022|Y|Cycle 5 Day 1|501|11MAR2022|EORTC QLQ-C30 Global Health Status|QL2|Global Health Status|41.666666667|25|16.666666667|
1001|17DEC2021|16SEP2022|Y|Cycle 6 Day 1|601|01APR2022|EORTC QLQ-C30 Global Health Status|QL2|Global Health Status|50|25|25|
1001|17DEC2021|16SEP2022|Y|Cycle 7 Day 1|701|21APR2022|EORTC QLQ-C30 Global Health Status|QL2|Global Health Status|50|25|25|
1001|17DEC2021|16SEP2022|Y|Cycle 8 Day 1|801|13MAY2022|EORTC QLQ-C30 Global Health Status|QL2|Global Health Status|66.666666667|25|41.666666667|
1001|17DEC2021|16SEP2022|Y|Cycle 9 Day 1|901|02JUN2022|EORTC QLQ-C30 Global Health Status|QL2|Global Health Status|58.333333333|25|33.333333333|
1001|17DEC2021|16SEP2022|Y|Cycle 10 Day 1|1001|24JUN2022|EORTC QLQ-C30 Global Health Status|QL2|Global Health Status|58.333333333|25|33.333333333|
1001|17DEC2021|16SEP2022|Y|Cycle 11 Day 1|1101|14JUL2022|EORTC QLQ-C30 Global Health Status|QL2|Global Health Status|33.333333333|25|8.3333333333|
1001|17DEC2021|16SEP2022|Y|Cycle 12 Day 1|1201|05AUG2022|EORTC QLQ-C30 Global Health Status|QL2|Global Health Status|50|25|25|
1001|17DEC2021|16SEP2022|Y|Cycle 13 Day 1|1301|25AUG2022|EORTC QLQ-C30 Global Health Status|QL2|Global Health Status|66.666666667|25|41.666666667|
1001|17DEC2021|16SEP2022|Y|Cycle 14 Day 1|1401|16SEP2022|EORTC QLQ-C30 Global Health Status|QL2|Global Health Status|50|25|25|
;
run;
options ls=max;
%let last_cycle_avisitn = 26;
data cycle_lookup;
set visits;
where AVISITN <= &last_cycle_avisitn.01;
run;
data want2;
/* array for mising visits */
array v[2:&last_cycle_avisitn.] _temporary_;
/* array for visits names */
array vn[2:&last_cycle_avisitn.] _temporary_;
array vc[2:&last_cycle_avisitn.] $ 20 _temporary_;
if _N_=1 then do until(EOF1);
set cycle_lookup end=EOF1;
vn[round(AVISITN/100,1)]=AVISITN;
vc[round(AVISITN/100,1)]=AVISIT;
put _all_;
end;
set have;
by USUBJID PARAMCD;
if first.PARAMCD then call missing(of v[*]);
v[round(AVISITN/100,1)] = 1;
output;
if last.PARAMCD then
do _N_ = 2 to &last_cycle_avisitn.;
if ^v[_N_] then
do;
AVISITN=vn[_N_];
AVISIT =vc[_N_];
ADT =.;
AVAL=0;
output;
end;
end;
run;
proc print data = want2;
run;
?
Bart
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.