Transposing a Row into a Column

[ciccotelli412]

Hey Everyone,

Quick (hopefully simple) question

I have the below data as table name "GC_SGRP_BREAKOUT"

HSHLD_NO            SGRP_DSCR                              SPEND_TY

00010005          SPECIALITY GIFT CARDS                     25

00010005          HOME GIFT CARDS                               50

00010005          FASHION GIFT CARDS                          50

00010005          RESTAURANTS GIFT CARDS              105

Basically I want it to read as this :

HSHLD_NO    SPECIALITY          HOME       FASHION      RESTAURANTS 

00010005            25                          50                 50                      105

How do i make this happen in SAS?

Thank you in advance,

Nick

[Reeza]

PROC TRANSPOSE

[ciccotelli412]

Can you be a little more specific? Like what the full code would be? 

[Reeza]

If you don't know how to find examples for a procedure in the documentation, that's a good thing to learn. Navigate to the procedure name and then it has a section/tab titled Examples. There's usually the most basic case, which is yours, and then more advanced examples as well.  Here's a few links to get you started.

Documentation

http://support.sas.com/documentation/cdl/en/proc/70377/HTML/default/viewer.htm#p1n9l1b2dg00w9n1h3tov...

http://support.sas.com/documentation/cdl/en/proc/70377/HTML/default/viewer.htm#n01rzqgzs8vq1bn10h1wt...

UCLA walk through

https://stats.idre.ucla.edu/sas/modules/how-to-reshape-data-long-to-wide-using-proc-transpose/

LexJansen.com search results

http://lexjansen.com/search/searchresults.php?q=proc%20transpose

[Astounding]

For a beginner, PROC TRANSPOSE is not easy.  In this case, it's harder because you have to prepare your data before PROC TRANSPOSE can be applied.  Two simple preparatory steps ...

First, create a variable holding the names to be used after transposing:

data partway_there;

set gc_sgrp_breakout;

varname = scan(sgrp_dscr, 1);

run;

You can take a look at VARNAME, and expect to use it in an ID statement within PROC TRANSPOSE.

Second, sort  your data (if it's not already sorted):

proc sort data=partway_there;

by hshld_no;

run;

This permits you to use a BY statement within PROC TRANSPOSE.

Finally, head into the PROC TRANSPOSE:

proc transpose data=partway_there out=want;

by hshld_no;

id varname;

run;

You will have to add to that final TRANSPOSE to get it to function the way you would like.