topic Re: Shifting values down in SAS Programming

Shifting values down

anu1999 — Tue, 26 Jul 2016 20:45:37 GMT

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.

Re: Shifting values down

PGStats — Tue, 26 Jul 2016 20:56:20 GMT

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;

Re: Shifting values down

KachiM — Tue, 26 Jul 2016 21:19:02 GMT

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;

Re: Shifting values down

ballardw — Tue, 26 Jul 2016 22:03:21 GMT

Sort data by ID year.

Use Lag, pay attention to id

resort by id descending year

Re: Shifting values down

Ksharp — Wed, 27 Jul 2016 01:19:22 GMT


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;

Re: Shifting values down

anu1999 — Wed, 27 Jul 2016 13:53:57 GMT

Thanks that works.

Re: Shifting values down

anu1999 — Wed, 27 Jul 2016 13:54:56 GMT

Thanks, that works.

Re: Shifting values down

anu1999 — Wed, 27 Jul 2016 13:56:22 GMT

Thanks, that works too.

Re: Shifting values down

Tom — Wed, 27 Jul 2016 14:02:02 GMT

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;