Constant lag creating within a group

[Nirmol]

Hi

I have a following data set where I have uniue ids and each id has 7 lines (Day 1 to Day 7) of observation. I have sorted it by ID and Day. All three variables are in numeric (best12) format. 
Now, I want to create a lag values of NP for each ID. Each lag value should appear in a new column and it will start in following day and continue till day 7. For instance: for ID 342, LagD1 should take NP of Day 1 and it should appear from day 2 and continue till day 7. Similarly, LagD4 should take NP of day 4 and must first appear in day 5 and continue till day 7.

Please, see my have file and want file.

have

ID	Day	NP
117	1	12
117	2	8
117	3	7
117	4	9
117	5	9
117	6	4
117	7	6
342	1	23
342	2	12
342	3	-16
342	4	0
342	5	8
342	6	23
342	7	20

want

ID	Day	NP	LagD1	LagD2	LagD3	LagD4	LagD5	LagD6
117	1	12
117	2	8	12
117	3	7	12	8
117	4	9	12	8	7
117	5	9	12	8	7	9
117	6	4	12	8	7	9	9
117	7	6	12	8	7	9	9	4
342	1	23
342	2	12	23
342	3	-16	23	12
342	4	0	23	12	-16
342	5	8	23	12	-16	0
342	6	23	23	12	-16	0	8
342	7	20	23	12	-16	0	8	23

Can anyone help me with any sample code?

[ballardw · Accepted solution]

Please include example data in the form of a data step as shown below, pasted into a text box or code box opened on the forum using the </> or running man icon.

When we have to guess what types your variables are some suggested solutions may not work with your data.

This seems to work for your example data:

data have;
   input ID	Day	NP;
datalines;
117	1	12
117	2	8
117	3	7
117	4	9
117	5	9
117	6	4
117	7	6
342	1	23
342	2	12
342	3	-16
342	4	0
342	5	8
342	6	23
342	7	20
;

data want; 
   set have;
   retain lagd1-lagd6;
   by id;
   l1=lag(np);
   if first.id then call missing(of lagd1-lagd6);
   select (day);
      when (1)  ;
      when (2)  Lagd1 = l1;
      when (3)  Lagd2 = l1;
      when (4)  Lagd3 = l1;
      when (5)  Lagd4 = l1;
      when (6)  Lagd5 = l1;
      when (7)  Lagd6 = l1;
      otherwise ;
   end;
   drop l1;
run;

[Nirmol]

Thank you. It helped.

[Tom]

You could just use LAGn() function calls.

To handle the multiple groups use BY group processing to clear out the invalid values.

data have;
  input ID Day NP ;
cards;
117 1 12
117 2 8
117 3 7
117 4 9
117 5 9
117 6 4
117 7 6
342 1 23
342 2 12
342 3 -16
342 4 0
342 5 8
342 6 23
342 7 20
;
data want;
   set have;
   by id;
   lagd1=lag1(np);
   lagd2=lag2(np);
   lagd3=lag3(np);
   lagd4=lag4(np);
   lagd5=lag5(np);
   lagd6=lag6(np);
   array lags lagd1-lagd6;
   do _n_=day to dim(lags);
     lags[_n_]=.;
   end;
run;

[Tom]

You could shift the values over using an ARRAY.

Wrapping the SET inside a DO loop eliminates the need to worry about retaining the values.

data want;
  do until (last.id);
     set have;
     by id;
     length lagd1-lagd6 8;
     array lags lagd6-lagd1 np ;
     output;
     do _n_=1 to dim(lags)-1;
       lags[_n_]=lags[_n_+1];
     end;
  end;
run;