Hello all,
Running SAS 9.4. I have a table that looks like
Subject |
Product |
resp |
4 |
B |
3 |
7 |
A |
1 |
9 |
B |
3 |
25 |
C |
4 |
data have;
input subject product $ resp;
datalines;
4 B 3
7 A 1
9 B 3
25 C 4
;
and would like it to look like this:
Product |
Subject4 |
Subject7 |
Subject9 |
Subject25 |
A |
. |
1 |
. |
. |
B |
3 |
. |
3 |
. |
C |
. |
. |
. |
4 |
I was able to accomplish this via some bulky code using arrays and proc transpose but am wondering if anyone has an elegant solution to this.
Thank you all.
data have;
input Subject Product $ resp;
cards;
4 B 3
7 A 1
9 B 3
25 C 4
;
run;
proc sort data =have;
by product ;
run;
proc transpose data =have out=want(drop=_name_) prefix=Subject;
by product;
id subject;
run;
data have;
input Subject Product $ resp;
cards;
4 B 3
7 A 1
9 B 3
25 C 4
;
run;
proc sort data =have;
by product ;
run;
proc transpose data =have out=want(drop=_name_) prefix=Subject;
by product;
id subject;
run;
Here you go:
data have;
input subject product $ resp;
datalines;
4 B 3
7 A 1
9 B 3
25 C 4
;run;
proc sort data=have;by product;
proc transpose data=have out=want (drop=_NAME_) prefix=Subject;by product;id subject;
You have an answer, however is the output you post the best way forward? I mean, how are you going to know what the variables are called, there doesn't appear to be a logical assignment. Why not, if you have to transpose the data at all, creat the variables as a generic, then assign labels to the generic variables, this will make further processing far easier for you:
proc transpose data=have out=want; by product; var resp; label=subject; run;
What you will get is a dataset, something like:
PRODUCT VAR1 VAR2 VAR3 VAR4
...
With VAR1 label as _4, VAR2 as _7 etc.
Then in your programming you can refer to var1-var4, or as an array of var{4}. None of which you can use if you don't know the variables.
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.