DATA Step, Macro, Functions and more

LAG function

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 6
Accepted Solution

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

Posted in reply to Manikanta

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".

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

View solution in original post


All Replies
Contributor RM6
Contributor
Posts: 24

Re: LAG function

Posted in reply to Manikanta
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

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

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

Re: LAG function

Posted in reply to Manikanta

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".

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

Re: LAG function

Posted in reply to KurtBremser
Many Thanks.
Super User
Posts: 10,574

Re: LAG function

Posted in reply to Manikanta

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

Posted in reply to Manikanta
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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 259 views
  • 1 like
  • 4 in conversation