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?
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;
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;
Thank you. It helped.
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.