Is there a way to proc transpose a column with multiple values into columns

[LL5]

Hi all, this may sound incompetent but I would like to explore the possibilities to proc transpose columns with different values into correspond columns. 

Let's say I have a dataset with 3 columns – type, id and location. See below

DATA TEST;

INPUT TYPE$1 ID LOCATION$;

CARDS;

A 1 E1

A 2 E2

B 1 W1

B 2 W2

C 1 N1

C 2 N2

;

RUN;

type	id	location
A	1	E1
A	2	E2
B	1	W1
B	2	W2
C	1	N1
C	2	N2

Now I want to transpose the type column into 3 different columns shown as below-

ID	A	B	C
1			N1
1		W1
1	E1
2			N2
2	E2
2		W2

This could be simply accomplished using case when in proc sql (like below) or if then else in data step.

However, I want to find out if there is a way to do it in proc transpose?

The reason I asked is because in my real work situation, I have more than 100K+ records with many different values under "type" columns, so I try to avoid hard coding like this. Thanks.

PROC SQL;

CREATE TABLE TEST2 AS

SELECT

ID,

CASE WHEN TYPE = 'A' THEN LOCATION ELSE " " END AS A,

CASE WHEN TYPE = 'B' THEN LOCATION ELSE " " END AS B,

CASE WHEN TYPE = 'C' THEN LOCATION ELSE " " END AS C

FROM TEST

ORDER BY ID

;

QUIT;

[Reeza]

Yes, this is possible. Do you really want them in separate rows though? Or all on a single row. Try the solutions below - note that SORTING is required for the By statement.

proc sort data=test;
by id;
run;

data have;
set test;
by ID;
if first.id then count=1; else count+1;
run;

proc transpose data=have out=want1;
id type;
by ID count;
var Location;
run;


*usually what people want;
proc transpose data=test out=want2;
id type;
by ID;
var Location;
run;

---

@LL5 wrote:

Hi all, this may sound incompetent but I would like to explore the possibilities to proc transpose columns with different values into correspond columns. 

Let's say I have a dataset with 3 columns – type, id and location. See below

DATA TEST;

INPUT TYPE$1 ID LOCATION$;

CARDS;

A 1 E1

A 2 E2

B 1 W1

B 2 W2

C 1 N1

C 2 N2

;

RUN;

type	id	location
A	1	E1
A	2	E2
B	1	W1
B	2	W2
C	1	N1
C	2	N2

Now I want to transpose the type column into 3 different columns shown as below-

ID	A	B	C
1			N1
1		W1
1	E1
2			N2
2	E2
2		W2

This could be simply accomplished using case when in proc sql (like below) or if then else in data step.

However, I want to find out if there is a way to do it in proc transpose?

The reason I asked is because in my real work situation, I have more than 100K+ records with many different values under "type" columns, so I try to avoid hard coding like this. Thanks.

PROC SQL;

CREATE TABLE TEST2 AS

SELECT

ID,

CASE WHEN TYPE = 'A' THEN LOCATION ELSE " " END AS A,

CASE WHEN TYPE = 'B' THEN LOCATION ELSE " " END AS B,

CASE WHEN TYPE = 'C' THEN LOCATION ELSE " " END AS C

FROM TEST

ORDER BY ID

;

QUIT;

---

[LL5]

Thanks Reeza for pointing out proc sort is required and showing me the technique that most people would use.

[Tom · Accepted solution]

You need to sort the data by ID to get PROC TRANSPOSE to operate on each ID group separately . You can use the ID statement to tell it to use TYPE as the name of the  new variable.  To get that goofy patchwork output you need to also include TYPE in the BY statement. 

proc sort data=test; by id type; run;

proc transpose data=test out=want1 ;
 by id type;
 id type;
 var location;
run;

proc print data=want1;
run;

Without that it will collapse to one observation per ID group.

proc transpose data=test out=want2 ;
 by id ;
 id type;
 var location;
run;

proc print data=want2;
run;

[LL5]

Thanks Tom for showing me two different approaches, this is very helpful.