topic Re: Lag in Data Loop in New SAS User

Lag in Data Loop

Will_FBB — Fri, 06 Sep 2019 16:07:13 GMT

How do I calculate y so the first ID will be equal to x, while the subsequent y in Month 2 - 10 will be previous y - previous a - previous b - previous c for each ID?

I am not sure if lag function is the best solution for this, it does not calculate the way I wanted.

data test;
set test_data end=eof;
by ID;

do _n_=1 until (eof);
if first.ID then y=x;
y+lag(y)-lag(a)-lag(b)-lag(c);
output;
end;
run;

attach shows finished table that I want (before and after)

Re: Lag in Data Loop

novinosrin — Fri, 06 Sep 2019 17:25:35 GMT

Hello @Will_FBB See if this helps

data have;
input ID $	Month	(x	a	b	c) (:comma10.);
cards;
A	1	 12,810 	 . 	 . 	 . 
A	2	 . 	 50 	 . 	 . 
A	3	 . 	 . 	 50 	 . 
A	4	 . 	 . 	 . 	 50 
A	5	 . 	 . 	 . 	 . 
A	6	 . 	 . 	 . 	 2,020 
A	7	 . 	 . 	 . 	 2,000 
A	8	 . 	 . 	 . 	 29 
A	9	 . 	 . 	 . 	 300 
A	10	 . 	 . 	 . 	 . 
B	1	 26,121 	 . 	 . 	 . 
B	2	 . 	 1,000 	 . 	 . 
B	3	 . 	 . 	 . 	 . 
B	4	 . 	 . 	 600 	 2,035 
B	5	 . 	 . 	 . 	 . 
B	6	 . 	 500 	 . 	 . 
B	7	 . 	 . 	 . 	 . 
B	8	 . 	 . 	 . 	 533 
B	9	 . 	 . 	 . 	 484 
B	10	 . 	 . 	 . 	 . 
;



data want;
 do until(last.id);
  set have;
  by id;
  array t(*) a--c;
  array u(3) _temporary_ ;
  retain _i _i1;
  if _n_=1 then do;
  _i=addrlong(t(1));
  _i1=addrlong(u(1));
  end;
  if first.id then y=x;
  else if n(of u(*))>0 then y+ (-sum(of u(*)));
  output;
  call missing(of u(*));
  call pokelong(peekclong(_i,dim(t)*8),_i1,dim(t)*8);
 end;
 call missing(y,of u(*));
 drop _:;
run;

Re: Lag in Data Loop

Will_FBB — Fri, 06 Sep 2019 17:48:22 GMT

Thank you so much! this works perfectly!!

Re: Lag in Data Loop

novinosrin — Fri, 06 Sep 2019 18:35:08 GMT

Hello @Will_FBB I think i overlooked something and unnecessarily complicated the previous. Here is a simple one



data have;
input ID $	Month	(x	a	b	c) (:comma10.);
cards;
A	1	 12,810 	 . 	 . 	 . 
A	2	 . 	 50 	 . 	 . 
A	3	 . 	 . 	 50 	 . 
A	4	 . 	 . 	 . 	 50 
A	5	 . 	 . 	 . 	 . 
A	6	 . 	 . 	 . 	 2,020 
A	7	 . 	 . 	 . 	 2,000 
A	8	 . 	 . 	 . 	 29 
A	9	 . 	 . 	 . 	 300 
A	10	 . 	 . 	 . 	 . 
B	1	 26,121 	 . 	 . 	 . 
B	2	 . 	 1,000 	 . 	 . 
B	3	 . 	 . 	 . 	 . 
B	4	 . 	 . 	 600 	 2,035 
B	5	 . 	 . 	 . 	 . 
B	6	 . 	 500 	 . 	 . 
B	7	 . 	 . 	 . 	 . 
B	8	 . 	 . 	 . 	 533 
B	9	 . 	 . 	 . 	 484 
B	10	 . 	 . 	 . 	 . 
;


data want;
 do until(last.id);
  set have;
  by id;
  array t(*) a--c;
  if first.id then y=x;
  else if s>. then y+(-s);
  output;
  call missing(s);
  if n(of t(*))>0 then s=sum(of t(*));
 end;
 call missing(y);
 drop s;
run;

Please accept my apology