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;
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;
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;
Thanks Reeza for pointing out proc sort is required and showing me the technique that most people would use.
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;
Thanks Tom for showing me two different approaches, this is very helpful.
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.