## LAG function

Solved
Occasional Contributor
Posts: 6

# LAG function

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

Accepted Solutions
Solution
‎11-28-2017 07:01 AM
Super User
Posts: 10,574

## Re: LAG function

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;
``````

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code

All Replies
Contributor
Posts: 24

## Re: LAG function

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;
Occasional Contributor
Posts: 6

## Re: LAG function

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

Solution
‎11-28-2017 07:01 AM
Super User
Posts: 10,574

## Re: LAG function

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;
``````

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Occasional Contributor
Posts: 6

Many Thanks.
Super User
Posts: 10,574

## Re: LAG function

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super User
Posts: 10,850

## Re: LAG function

``````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;``````
☑ This topic is solved.