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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.