Help using Base SAS procedures

Is there a way to retain values by ID?

Reply
Contributor
Posts: 58

Is there a way to retain values by ID?

So my dataset is in long format. I am trying to calculate the change of scores from baseline. I have created the following macro:

%MACRO CHANGE(score,var);

    if visit=1 then &score._bl=&score;

retain &score._bl;

else &score._chg=&score-&score._bl;

label &score._bl="&var at baseline"

&score._chg="Change in &var from baseline";

%MEND CHANGE;


So the issue I am running into, is that if a patient did not have baseline (visit) assessment, the code takes the last known baseline value from another patient, which I do not want.


This is my sample data (based after running the macro above and looking at my data):


IDVisitScore (&score)
Score at Baseline (&score._bl)Change in Score from Baseline
1112.512.5
122012.57.5
222612.513.5


So as you can see highlighted in red, ID=2 did not have a visit 1, he joined the study at visit 2. But when the macro calculated the change of score, the previous retained value is used. THis is not right. Is there a way I can fix this?


Alternatively, I can create a new dataset with just the baseline values by ID, rename the variables and merge it back in. But I am hoping there is a more efficient way to do this.


Reason why I am using a macro approach is because I will be doing this with multiple scores.


I would like to thank everyone in advanced who provided feedback on this.

Trusted Advisor
Posts: 3,215

Re: Is there a way to retain values by ID?

processing grouped data is possible with first.<var> and/or last.<var> conditions with a  set dataset ... ;  by <var>    processing.

As the dataset and ordering is important I do not see the intention of using the macro change approach.

---->-- ja karman --<-----
Trusted Advisor
Posts: 1,231

Re: Is there a way to retain values by ID?

Hi,

Not sure about above macro logic. May be this can provide the desired output.

data have;
input id visit score;
datalines;
1 1 12.5
1 2 20
2 2 26
;

data want;
set have;
by id;
retain base_line;
if first.id then base_line=score;
change=score-base_line;
run;

Contributor
Posts: 58

Re: Is there a way to retain values by ID?

reason why I am using a macro approach is i have to do this for about 30 difference scores

Super User
Super User
Posts: 7,078

Re: Is there a way to retain values by ID?

How about something like this?

%MACRO CHANGE(score,var,byvar);

%if 0=%length(var) %then %let var=&score;

%if 0=&length(byvar) %then %let byvar=_n_;

%else %let byvar=first.&byvar;

if &byvar=1 then &score._bl=.;

if visit=1 then &score._bl=&score;

else if &score._bl ne . then &score._chg=&score-&score._bl;

retain &score._bl;

label &score._bl="&var at baseline"

      &score._chg="Change in &var from baseline"

;

%MEND CHANGE;

data want ;

  set have ;

  by patid;

  %change(HAMD,,patid)

run;

Message was edited by: Tom Abernathy to make macro a little smarter.

Contributor
Posts: 58

Re: Is there a way to retain values by ID?

I am starting to think about what you posted. Sadly a simple copy and paste did not work.  So I am starting to think about having a first combination of ID and visit, since that is how the record is uniquely identified, but I do somewhat understand your approach to this issue.

Also ID variable is numeric so can't use length.

Super User
Posts: 5,518

Re: Is there a way to retain values by ID?

Tpham,

Just one point of view on this, but you would become a much better SAS programmer much faster if you were to forget about macro language entirely.  Remember, macro language only creates the SAS program but it doesn't process the data.  If you expand your set of SAS tools you will find that you can solve more problems without macro language, with the added bonus that if you do need macro language you will have a wider variety of programming techniques that you can use to approach the problem.

All that being said, here is one approach to handle many scores.  Create 3 arrays.  One holds the baseline scores (this will be a temporary array), one holds the current scores, and one holds the changes from baseline.  This would be the form that the DATA step could take:

data want;

   set have;

   by patid; 

   array changes {30} change1-change30;

   array scores {30} score1-score30;

   array baseline {30} _temporary_;

   if first.patid then do _i_=1 to 30;

      baseline{_i_} = score{_i_};

   end;

   else do _i_=1 to 30;

      changes{_i_} = scores{_i_} - baseline{_i_};

   end;

run;

Note that elements of a _temporary_ array are not variables (so do they do not need to be dropped), and they are automatically retained.

Good luck.

Super User
Super User
Posts: 7,078

Re: Is there a way to retain values by ID?

You might want to add another ELSE statement to force the new change variable to missing when the baseline is not known.

Ask a Question
Discussion stats
  • 7 replies
  • 242 views
  • 8 likes
  • 5 in conversation