topic Re: Transposing a Row into a Column in SAS Procedures

Transposing a Row into a Column

ciccotelli412 — Thu, 10 Aug 2017 18:35:06 GMT

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

Re: Transposing a Row into a Column

Reeza — Thu, 10 Aug 2017 18:34:42 GMT

PROC TRANSPOSE

Re: Transposing a Row into a Column

ciccotelli412 — Thu, 10 Aug 2017 18:35:47 GMT

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

Re: Transposing a Row into a Column

Reeza — Thu, 10 Aug 2017 18:45:49 GMT

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#p1n9l1b2dg00w9n1h3tovj0rwlos.htm

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

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

Re: Transposing a Row into a Column

Astounding — Thu, 10 Aug 2017 19:07:32 GMT

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.