topic Convert multiple rows to different columns in SAS Programming

Convert multiple rows to different columns

Abby7 — Tue, 23 Feb 2021 02:59:39 GMT

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

Re: Convert multiple rows to different columns

qoit — Tue, 23 Feb 2021 03:49:11 GMT

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;

Re: Convert multiple rows to different columns

mkeintz — Tue, 23 Feb 2021 05:34:07 GMT

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