Help transposing/grouping a table

[spirto]

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.

[mohamed_zaki · Accepted solution]

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;

[Steelers_In_DC]

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;

[RW9]

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.