LAG function

[Manikanta]

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

[RM6]

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;

[Manikanta]

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

[Kurt_Bremser · Accepted solution]

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

[Manikanta]

Many Thanks.

[Kurt_Bremser]

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

[Ksharp]

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;