Hello,
I'm creating a parameter for a dataset and for each result (AVAL) there is a particular strategy (Composite Strategy 2). For this one I need to impute the AVAL from the last visit (DTYPE=LOV) (In this case Early termination and if an AVAL is present in that visit I need to use that AVAL for previous visits where AVAL is missing. My question is how do I do a double DOW loop to achieve this and get the missing AVAL records to populate with the AVAL from the Early Termination visit but without overwriting any visits where AVAL is present (In this example data the Week 2 AVAL). Here is a snippet of my current dataset and my desired output
data have;
input PARAMCD $ STRATEGY :$30. DTYPE $ SUBJID $ VISIT :$30. AVAL :8.;
infile datalines dlm = '|';
datalines;
TOTEDPS1|Composite strategy 2|LOV|1001|Week 2|61.2
TOTEDPS1|Composite strategy 2|LOV|1001|Week 4|
TOTEDPS1|Composite strategy 2|LOV|1001|Week 8|
TOTEDPS1|Composite strategy 2|LOV|1001|Week 12|
TOTEDPS1|Composite strategy 2|LOV|1001|Week 16|
TOTEDPS1|Composite strategy 2|LOV|1001|Early Termination|43.5
TOTEDPS1|Composite strategy 2|LOV|1002|Week 2|16
TOTEDPS1|Composite strategy 2|LOV|1002|Week 4|
TOTEDPS1|Composite strategy 2|LOV|1002|Week 8|
TOTEDPS1|Composite strategy 2|LOV|1002|Week 12|
TOTEDPS1|Composite strategy 2|LOV|1002|Week 16|
TOTEDPS1|Composite strategy 2|LOV|1002|Early Termination|6.6
;
run;
data want;
input PARAMCD $ STRATEGY :$30. DTYPE $ SUBJID $ VISIT :$30. AVAL :8.;
infile datalines dlm = '|';
datalines;
TOTEDPS1|Composite strategy 2|LOV|1001|Week 2|61.2
TOTEDPS1|Composite strategy 2|LOV|1001|Week 4|43.5
TOTEDPS1|Composite strategy 2|LOV|1001|Week 8|43.5
TOTEDPS1|Composite strategy 2|LOV|1001|Week 12|43.5
TOTEDPS1|Composite strategy 2|LOV|1001|Week 16|43.5
TOTEDPS1|Composite strategy 2|LOV|1001|Early Termination|43.5
TOTEDPS1|Composite strategy 2|LOV|1002|Week 2|16
TOTEDPS1|Composite strategy 2|LOV|1002|Week 4|6.6
TOTEDPS1|Composite strategy 2|LOV|1002|Week 8|6.6
TOTEDPS1|Composite strategy 2|LOV|1002|Week 12|6.6
TOTEDPS1|Composite strategy 2|LOV|1002|Week 16|6.6
TOTEDPS1|Composite strategy 2|LOV|1002|Early Termination|6.6
;
run;
Actually figured it out myself. Here is the code for anyone interested.
data want;
do _n_ = 1 by 1 until(last.usubjid);
set have;
by usubjid;
if visit ='Early Termination' and not(missing(aval)) then last_non_missing = aval;
end;
do _n_ = 1 to _n_;
set have;
aval =coalesce(aval,last_non_missing);
output;
end;
drop last_non_missing;
run;
Actually figured it out myself. Here is the code for anyone interested.
data want;
do _n_ = 1 by 1 until(last.usubjid);
set have;
by usubjid;
if visit ='Early Termination' and not(missing(aval)) then last_non_missing = aval;
end;
do _n_ = 1 to _n_;
set have;
aval =coalesce(aval,last_non_missing);
output;
end;
drop last_non_missing;
run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.