DATA Step, Macro, Functions and more

Help transposing/grouping a table

Accepted Solution Solved
Reply
Contributor
Posts: 38
Accepted Solution

Help transposing/grouping a table

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.


Accepted Solutions
Solution
‎01-28-2016 09:48 AM
Super Contributor
Posts: 490

Re: Help transposing/grouping a table

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;

View solution in original post


All Replies
Solution
‎01-28-2016 09:48 AM
Super Contributor
Posts: 490

Re: Help transposing/grouping a table

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;
Valued Guide
Posts: 858

Re: Help transposing/grouping a table

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;

Super User
Super User
Posts: 7,392

Re: Help transposing/grouping a table

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.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 204 views
  • 1 like
  • 4 in conversation