Is there a way to retain values by ID?

[Tpham]

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):

ID	Visit	Score (&score)	Score at Baseline (&score._bl)	Change in Score from Baseline
1	1	12.5	12.5
1	2	20	12.5	7.5
2	2	26	12.5	13.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.

[jakarman]

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.

[stat_sas]

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;

[Tpham]

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

[Tom]

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.

[Tpham]

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.

[Astounding]

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.

[Tom]

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