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;
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.