topic Re: Help transposing/grouping a table in SAS Programming

Help transposing/grouping a table

spirto — Wed, 27 Jan 2016 22:13:28 GMT

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.

Re: Help transposing/grouping a table

mohamed_zaki — Wed, 27 Jan 2016 22:24:38 GMT

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;

Re: Help transposing/grouping a table

Steelers_In_DC — Wed, 27 Jan 2016 22:24:49 GMT

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;

Re: Help transposing/grouping a table

RW9 — Thu, 28 Jan 2016 11:30:17 GMT

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.