Double DOW loop to populate missing AVAL records from Last on visit

smackerz1988 — Sat, 22 Oct 2022 14:43:30 GMT

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;

Re: Double DOW loop to populate missing AVAL records from Last on visit

smackerz1988 — Sat, 22 Oct 2022 15:01:35 GMT

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;

topic Re: Double DOW loop to populate missing AVAL records from Last on visit in SAS Programming

Double DOW loop to populate missing AVAL records from Last on visit

Re: Double DOW loop to populate missing AVAL records from Last on visit