Help using Base SAS procedures

Transposing a Row into a Column

Reply
New Contributor
Posts: 4

Transposing a Row into a Column

[ Edited ]

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

Super User
Posts: 17,784

Re: Transposing a Row into a Column

PROC TRANSPOSE

New Contributor
Posts: 4

Re: Transposing a Row into a Column

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

Super User
Posts: 17,784

Re: Transposing a Row into a Column

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

 

Super User
Posts: 5,079

Re: Transposing a Row into a Column

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.

Ask a Question
Discussion stats
  • 4 replies
  • 142 views
  • 0 likes
  • 3 in conversation