Needing some help with converting a data set with multiple rows for the same data set to multiple columns.
An example of the current data set is:
ID Number | Type |
1 | A |
1 | AA |
2 | A |
2 | AAA |
2 | AAAAAAA |
3 | AAAAAAAAAA |
4 | AA |
4 | A |
5 | A |
5 | AAAAAAA |
5 | AAAA |
An example of what the data set needs to look like is:
ID Number | Type | Type 2 | Type 3 | Type 4 | Type 5 |
1 | A | AA | |||
2 | A | AAA | AAAAAAA | AAAAAAAAAA | |
3 | AAAAAAAAAA | ||||
4 | AA | A | |||
5 | A | AAAAAAA | AAAA |
I have tried a proc transpose however that has resulted in an output like this:
ID Number | Type A | Type 2 | Type 3 | Type 4 | Type 5 | Type 6 | Type 7 | Type 8 | Type 9 |
1 | 1 | ||||||||
2 | 1 | 1 | 1 | 1 | |||||
3 | 1 | ||||||||
4 | 1 | 1 | |||||||
5 | 1 | 1 | 1 |
Thank you for your help
Hope the below helps:
data have;
infile datalines missover;
input id type$;
datalines;
1 A
1 AA
2 A
2 AAA
2 AAAAAAA
3 AAAAAAAAAA
4 AA
4 A
5 A
5 AAAAAAA
5 AAAA
;
run;
data have1;
set have;
by id;
retain cnt 0;
if first.id then
cnt = 0;
cnt + 1;
run;
proc sql;
select max(id)
into :max_id TRIMMED
from have1;
quit;
%put &=max_id;
data want (keep=id type1 - type&max_id.);
set have1;
by id;
retain type1 - type&max_id.;
array types{&max_id.} $30. type1 - type&max_id.;
if first.id then
do i = 1 to &max_id.;
types{i} = .;
end;
types{cnt} = type;
if last.id then
output;
run;
It looks to me like the proc transpose produces what you want:
data have;
infile datalines missover;
input id type$;
datalines;
1 A
1 AA
2 A
2 AAA
2 AAAAAAA
3 AAAAAAAAAA
4 AA
4 A
5 A
5 AAAAAAA
5 AAAA
run;
proc transpose data=have out=want (drop=_name_) prefix=type_ ;
by id;
var type;
run;
proc print;run;
The result is:
Obs id type_1 type_2 type_3
1 1 A AA
2 2 A AAA AAAAAAA
3 3 AAAAAAAA
4 4 AA A
5 5 A AAAAAAA AAAA
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.