topic Re: LAG function in SAS Programming

LAG function

Manikanta — Tue, 28 Nov 2017 10:19:06 GMT

data one;
input ID $ number ;
datalines;
a1 100
a1 150
a1 200
a2 125
a2 150
a2 300
a2 400

;
run;

Can anyone help me how can I get the below output .

Output :

ID number c1 c2 c3
a1 100 .
a1 150 100
a1 200 150 100
a2 125 .
a2 150 125
a2 300 150 125
a2 400 300 150 125

Re: LAG function

RM6 — Tue, 28 Nov 2017 10:40:16 GMT

this can be achieved by using lag function and first.
some thing like this

data two;
set one;
by id;
c1=lag(number);
if first.id then c1=.;
run;
proc sort data=two; by id number c1;run;
data two;
set two;
by id number c1;
c2=lag(c1);
if first.id then c2=.;
run;
proc sort data=two; by id number c1 c2;run;
data two;
set two;
by id number c1 c2;
c3=lag(c2);
if first.id then c3=.;
run;

Re: LAG function

Kurt_Bremser — Tue, 28 Nov 2017 11:33:44 GMT

How do you know the maximum number of rows per id in dataset one?

Re: LAG function

Manikanta — Tue, 28 Nov 2017 11:36:07 GMT

Thanks for your reply .
But what if we have more data .
For example :
a1 3
a1 4
a1 5
a1 6
a1 7
a1 8
a1 9.
.
.
.
N

Re: LAG function

Kurt_Bremser — Tue, 28 Nov 2017 11:56:16 GMT

Determine the number of needed variables first, and then use array processing:

data one;
input ID $ number ;
datalines;
a1 100
a1 150
a1 200
a2 125
a2 150
a2 300
a2 400
a3 3
a3 4
a3 5
a3 6
a3 7
a3 8
a3 9
;
run;

proc sql noprint;
select max(counter) - 1 into :counter from (select count(*) as counter from one group by id);
quit;

%let counter=&counter; *removes blanks;

data want;
set one;
by id;
array targets {&counter} c1-c&counter;
retain c1-c&counter;
if first.id
then do;
  i = 1;
  do i1 = 1 to &counter;
    targets{i1} = .;
  end;
end;
else i + 1;
do i1 = i-1 to 2 by -1;
  targets{i1} = targets{i1-1};
end;
targets{1} = lag(number);
if first.id then targets{1} = .;
drop i i1;
run;

I added your second data example as group "a3".

Re: LAG function

Manikanta — Tue, 28 Nov 2017 12:03:55 GMT

Many Thanks.

Re: LAG function

Ksharp — Tue, 28 Nov 2017 13:16:51 GMT

data one;
input ID $ number ;
datalines;
a1 100
a1 150
a1 200
a2 125
a2 150
a2 300
a2 400
;
run;

data want;
 set one;
 lag=lag(number);
 lag2=lag2(number);
 if id ne lag(id) then call missing(lag);
 if id ne lag2(id) then call missing(lag2);
run;