BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Nirmol
Fluorite | Level 6

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

IDDayNP
117112
11728
11737
11749
11759
11764
11776
342123
342212
3423-16
34240
34258
342623
342720

want

IDDayNPLagD1LagD2LagD3LagD4LagD5LagD6
117112      
1172812     
11737128    
117491287   
1175912879  
11764128799 
117761287994
342123      
34221223     
3423-162312    
342402312-16   
342582312-160  
3426232312-1608 
3427202312-160823

Can anyone help me with any sample code?

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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;

View solution in original post

4 REPLIES 4
ballardw
Super User

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
Fluorite | Level 6

Thank you. It helped.

Tom
Super User Tom
Super User

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
Super User Tom
Super User

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;

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 475 views
  • 2 likes
  • 3 in conversation