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

Hi I am trying to get an output which is similar to creating lags but lag functions doesn't give me desired output. Please suggest how to get the output like  shown below.

Mainly I want to create a new variable "l_no" which looks back to last year value from "no" variable by id and yr variable (if no value available for last year then missing).

 

data sin_x;
input id yr no;
datalines;

1 2015 41
1 2014 2
2 2012 3
2 2011 4
3 2010 7
3 2009 0
3 2008 4
4 2007 0
4 2006 7

;

 

Desired output:

 

id yr     no   l_no
1  2015  41    2
1  2014  2      .
2  2012  3     4
2  2011  4     .
3  2010  7    0
3  2009  0    4
3  2008  4    .
4  2007  0    7
4  2006  7    .

 

Appreciate your help.

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

data sin_x;
input id yr no;
datalines;
1 2015 41 
1 2014 2 
2 2012 3 
2 2011 4 
3 2010 7
3 2009 0
3 2008 4
4 2007 0 
4 2006 7
;
run;
data want;
 if _n_=1 then do;
  if 0 then set sin_x(rename=(yr=_yr no=i_no));
  declare hash h(dataset:'sin_x(rename=(yr=_yr no=i_no))');
  h.definekey('id','_yr');
  h.definedata('i_no');
  h.definedone();
 end;
call missing(of _all_);
set sin_x;
_yr=yr-1;
rc=h.find();
drop _: rc;
run;




View solution in original post

8 REPLIES 8
PGStats
Opal | Level 21

Go for a left join:

 


proc sql;
create table desired as
select a.id, a.yr, b.no as l_no
from sin_x as a left join sin_x as b on a.id=b.id and a.yr=b.yr+1
order by id, yr desc;
select * from desired;
quit;
PG
anu1999
Obsidian | Level 7
Thanks that works.
KachiM
Rhodochrosite | Level 12

If you like a data step program here you go: 

 

The maximum group size is set to 3. This can be changed to the actual max number. It can be programatically found.

data sin_x;
input id yr no;
datalines;
1 2015 41 
1 2014 2 
2 2012 3 
2 2011 4 
3 2010 7
3 2009 0
3 2008 4
4 2007 0 
4 2006 7
;
run;

%let groupMax = 3;

data want;
   array k[&groupMax] _temporary_;
   do i = 1 by 1 until(last.id);
      set sin_x;
      by id;
      k[i] = no;
   end;
   
   do j = 1 to dim(k) -1;
      l_no = k[j + 1];
      output;
   end;
   call missing(of k[*]);
drop i j;
run;

proc print data = want;
run;

 

anu1999
Obsidian | Level 7
Thanks, that works too.
ballardw
Super User

Sort data by ID year.

Use Lag, pay attention to id

resort by id descending year

Ksharp
Super User

data sin_x;
input id yr no;
datalines;
1 2015 41 
1 2014 2 
2 2012 3 
2 2011 4 
3 2010 7
3 2009 0
3 2008 4
4 2007 0 
4 2006 7
;
run;
data want;
 if _n_=1 then do;
  if 0 then set sin_x(rename=(yr=_yr no=i_no));
  declare hash h(dataset:'sin_x(rename=(yr=_yr no=i_no))');
  h.definekey('id','_yr');
  h.definedata('i_no');
  h.definedone();
 end;
call missing(of _all_);
set sin_x;
_yr=yr-1;
rc=h.find();
drop _: rc;
run;




anu1999
Obsidian | Level 7
Thanks, that works.
Tom
Super User Tom
Super User

LAG does exactly what you want.  You just have your data sorted incorrectly. Also remember to call LAG() on every observation.

proc sort data=have; by id yr; run;
data want ;
  set have ;
  by id yr ;
  lag_no=lag(no);
  if not first.id then l_no=lag_no;
run;

Capture.PNG

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

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
  • 8 replies
  • 1737 views
  • 7 likes
  • 6 in conversation