cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
smackerz1988
Pyrite | Level 9 smackerz1988
Pyrite | Level 9
Go to Solution

Generate Missing Records for Deceased Subjects Up to a Specified Cycle using Hash Objects (Updated)

Posted 01-11-2024 09:05 AM (302 views)

Hello,

 

I have created a new post based off my previous post  to give full context to my issue as I had oversimplified previously.

 

Solved: Generating Missing Records for Deceased Subjects Up to a Specified Cyc... - SAS Support Comm...

 

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;

 

 

0 Likes
Reply
1 ACCEPTED SOLUTION

Accepted Solutions
yabwon
Onyx | Level 15 yabwon
Onyx | Level 15

Re: Generate Missing Records for Deceased Subjects Up to a Specified Cycle using Hash Objects (Updat

Posted 01-11-2024 09:36 AM (284 views)  |   In reply to smackerz1988

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

_______________
Polish SAS Users Group: www.polsug.com and communities.sas.com/polsug

"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings

SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation



View solution in original post

Reply
1 REPLY 1
yabwon
Onyx | Level 15 yabwon
Onyx | Level 15

Re: Generate Missing Records for Deceased Subjects Up to a Specified Cycle using Hash Objects (Updat

Posted 01-11-2024 09:36 AM (285 views)  |   In reply to smackerz1988

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

_______________
Polish SAS Users Group: www.polsug.com and communities.sas.com/polsug

"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings

SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation



Reply

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Watch How to Concatenate Values on YouTube

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 1 reply
  • ‎01-11-2024 09:05 AM
  • 303 views
  • 1 like
  • 2 in conversation