Retain a Value Across Data Steps

[BrettSD]

I have a data set with a name variable and a numeric value call it x, I want to create a new variable that will give a string to tell me if the previous 5 data steps that match that name had x > some number. For example:

Name x
Bill 108
Steve 77
Bill 99
Bill 150
Bill 121
Bill 56
Susan 108
Bill 95

I want the last Bill line to have a new variable that would be 'zyzzz' were the z indicates x > 100, y < 100, and then the next bill entry would have 'yzzzy' etc.

Any suggestions? Thanks.

 

[ChrisW75]

First off, I think your terminology might be wrong. A datastep is a block of SAS code starting "Data ...;" and ending "run;".
I assume what you mean here is that you want to check the previous 5 records, or observations as they are called in SAS.

You can keep values by using the RETAIN statement. The problem here is though that you'd effectively need to keep 5 variables for each person, which could be a real problem for you.

My suggestion would be to do the following.
I'm working on the assumption that you want the data to be kept in the same order.
1 - add a new variable called "order" which is a sequential number.

data dset2;
  set dset1;

  order = _n_;
run;

2 - Sort by name and then order.

proc sort data=dset2;
  by name order;
run;

3 - Now you can retain the last 5 values for each name, and reset when you reach a new name.

data dset3;
  set dset2;
  by name;
  retain last1-last5;

  if first.name then
  do;
   last1 = .; last2=.; ... last5=.;
  end;

  ...
run;

That should get you on the right track I reckon. Afterwards you can sort the datset by order and end up with the data back in the same order it was to start with.



Chris
Business Analyst, Code Monkey, Data Wrangler.
SAS Guru.