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
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".
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".
How do you know the maximum number of rows per id in dataset one?
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.