DATA Step, Macro, Functions and more

Create column from last non-missing value

Reply
Contributor
Posts: 28

Create column from last non-missing value

[ Edited ]
 
Contributor
Posts: 28

Re: BASE sas

Posted in reply to Bhargav_Movva

please make a correction  -->fourth record from last should contain 44 as base value instead of 120

Super User
Super User
Posts: 9,599

Re: BASE sas

Posted in reply to Bhargav_Movva

So its just retaining the row with the word screening?  Then its simple:

data want;
  set lab;
  retain base;
  if visit="screening" then base=lbstresn;
run;

However I suspect its more complicated than that.  I assume your creating an ADaM dataset, so want a baseline flag.  To do this first create a dataset with all the baseline values, then merge this back to your original data.  E.g.

data base (keep=subjid lbtest base_val (rename=(lbstresn=base_val));
  set lab;
  where visit="screening";
run;

data want;
  merge lb base;
  by subjid lbtest;
run;

You can then alter that to keep other variables, do more compicated baseline calculations, LOCF etc.

Super User
Posts: 6,751

Re: BASE sas

Posted in reply to Bhargav_Movva

Since you need to inspect both the baseline and predose values, two loops can do the trick:

 

data want;

do until (last.subjid);

   set have;

   by subjid;

   if visit in ('screening', 'predose') and lbstresn > . then base = lbstresn;

end;

do until (last.subjid);

   set have;

   by subjid;

   output;

end;

run;

Contributor
Posts: 28

Re: BASE sas

Posted in reply to Astounding

hey thank you so much it worked.But still i didnt understood the trick of two loops .please explain.Thanks

Super User
Posts: 6,751

Re: BASE sas

Posted in reply to Bhargav_Movva

The top loop reads through all the observations for a subject.  Whenever it finds a nonmissing value (for selected visit types) it updates BASE.  So BASE is established at that point.

 

Then the bottom DO loop reads exactly the same observations.  (When a DATA step contains multiple SET statements, each one operates independently of the other.)  It outputs those observations, along with the previously calculated value for BASE.

 

If you want to find more examples like this, you can search for DOW loops.

Ask a Question
Discussion stats
  • 5 replies
  • 79 views
  • 1 like
  • 3 in conversation