CODEWITHSUNDEEP
sas
Nathan123
Nathan123

Reputation: 763

SAS retain statement not working as I hoped

I have the following dataset

data have;
input SUBJID VISIT$ PARAMN  ABLF$ AVAL;
cards;
        1   screen      1   .       151
        1   random      1   YES     .
        1   visit1      1   .       .
        1   screen      2   .       65.5
        1   random      2   YES     65      
        1   visit1      2   .       .
        1   screen      3   .       .
        1   random      3   YES     400
        1   visit1      3   .       420
;
run;

I want to create another variable called BASE that captures the value of AVAL (when there is an actual value in place) when ABLF=YES and and then drag it down until a new PARAMN is encountered.

Basically I want the output to look like this

 SUBJID  VISIT$  PARAMN  ABLF$  AVAL BASE;

    1   screen      1   .       151     .
    1   random      1   YES     .       .
    1   visit1      1   .       .       .
    1   screen      2   .       65.5    65
    1   random      2   YES     65      65
    1   visit1      2   .       .       65
    1   screen      3   .       .       400
    1   random      3   YES     400     400
    1   visit1      3   .       420     400

I used the the following code 

data want;
set have;
by SUBJID PARAMN;
if first.PARAMN and ABLF=' ' then BASE=.;
if ABLF='YES' then BASE=AVAL;
retain BASE;
run;

however when I run this I don't the data to look exactly as I want above

Upvotes: 0

Views: 1158

Answers (2)

Tom
Tom

Reputation: 51581

RETAIN does not look like the right tool for this. RETAIN can only move data forward in the file. It cannot move it backwards.

Looks like there is just one observation with the "BASE" value. So just merge it back onto the data.

data want;
  merge have
        have(keep=subjid paramn aval ablf rename=(aval=BASE ablf=xx)
             where=(xx='YES'))
  ;
  by SUBJID PARAMN;
  drop xx;
run;

Upvotes: 2

Shenglin Chen
Shenglin Chen

Reputation: 4554

Pro SQL:

proc sql;
   select a.*,b.aval as BASE from have a left join have(drop=visit where=(ablf='YES')) b
   on a.subjid=b.subjid and a.paramn=b.paramn;
quit;

Double do loop:

data want;
    do until(last.visit);
    set have;
    retain temp;
    by subjid paramn notsorted;
    if ablf='YES' then temp=aval;
    end;
    do until(last.visit);
    set have;
    by subjid paramn notsorted;
    base=temp;
    end;
    drop temp;
run;

Upvotes: 0

Related Questions