topic Transpose Help! in SAS Programming

Transpose Help!

Ganeshk — Thu, 09 Jul 2015 16:20:26 GMT

Hi,

i have data set has shown below:

Category	Subcategory	Class	Item_Description	Item_ID	30/March/2015	6/April/2015	13/April/2015	20/April/2015	27/April/2015	4/May/2015
Snacks	Chips	Potato_Chips	Potato Lays 50gm	502223189	23	26	24	22	23	25
Snacks	Chips	Chilly_Chips	Chilly Lays 50gm	621452221	26	24	25	21	22	24

Required O/P:

Category	Subcategory	Class	Item_Description	Item_ID	Date	Price
Snacks	Chips	Potato_Chips	Potato Lays 50gm	502223189	30/March/2015	23
Snacks	Chips	Potato_Chips	Potato Lays 50gm	502223189	6/April/2015	26
Snacks	Chips	Potato_Chips	Potato Lays 50gm	502223189	13/April/2015	24
Snacks	Chips	Potato_Chips	Potato Lays 50gm	502223189	20/April/2015	22
Snacks	Chips	Potato_Chips	Potato Lays 50gm	502223189	27/April/2015	23
Snacks	Chips	Potato_Chips	Potato Lays 50gm	502223189	4/May/2015	25
Snacks	Chips	Chilly_Chips	Chilly Lays 50gm	621452221	30/March/2015	26
Snacks	Chips	Chilly_Chips	Chilly Lays 50gm	621452221	6/April/2015	24
Snacks	Chips	Chilly_Chips	Chilly Lays 50gm	621452221	13/April/2015	25
Snacks	Chips	Chilly_Chips	Chilly Lays 50gm	621452221	20/April/2015	21
Snacks	Chips	Chilly_Chips	Chilly Lays 50gm	621452221	27/April/2015	22
Snacks	Chips	Chilly_Chips	Chilly Lays 50gm	621452221	4/May/2015	24

How to transpose as shown above?

Thanks,

Ganesh K

Re: Transpose Help!

PGStats — Thu, 09 Jul 2015 17:34:44 GMT

You are asking for trouble with variable names like 30/April/2015. Are these column headers your variable names or labels? - PG

Re: Transpose Help!

SG_Help — Thu, 09 Jul 2015 18:12:52 GMT

I got the original data and saved it in an excel workbook, then I imported into SAS. Below is the code and output:

PROC IMPORT OUT=WORK.ORIGINAL

DATAFILE="DIRECTORY\Original.xlsx"

DBMS=EXCEL REPLACE;

RANGE="SHEET1$";

GETNAMES=YES;

MIXED=NO;

SCANTEXT=YES;

USEDATE=YES;

SCANTIME=YES;

RUN;

PROC SORT DATA=WORK.ORIGINAL;

BY CATEGORY SUBCATEGORY CLASS ITEM_DESCRIPTION ITEM_ID;

RUN;

PROC TRANSPOSE DATA=WORK.ORIGINAL OUT=T_ORIGINAL;

BY CATEGORY SUBCATEGORY CLASS ITEM_DESCRIPTION ITEM_ID;

RUN;

DATA TRANSPOSED;

SET T_ORIGINAL;

LABEL _LABEL_ ='Date' COL1='Price';

RENAME _LABEL_ =Date COL1=Price;

DROP _NAME_;

RUN;

Snacks	Chips	Chilly_Chips	Chilly Lays 50gm	621452221	30/March/2015	26
Snacks	Chips	Chilly_Chips	Chilly Lays 50gm	621452221	06/April/2015	24
Snacks	Chips	Chilly_Chips	Chilly Lays 50gm	621452221	13/April/2015	25
Snacks	Chips	Chilly_Chips	Chilly Lays 50gm	621452221	20/April/2015	21
Snacks	Chips	Chilly_Chips	Chilly Lays 50gm	621452221	27/April/2015	22
Snacks	Chips	Chilly_Chips	Chilly Lays 50gm	621452221	04/May/2015	24
Snacks	Chips	Potato_Chips	Potato Lays 50gm	502223189	30/March/2015	23
Snacks	Chips	Potato_Chips	Potato Lays 50gm	502223189	06/April/2015	26
Snacks	Chips	Potato_Chips	Potato Lays 50gm	502223189	13/April/2015	24
Snacks	Chips	Potato_Chips	Potato Lays 50gm	502223189	20/April/2015	22
Snacks	Chips	Potato_Chips	Potato Lays 50gm	502223189	27/April/2015	23
Snacks	Chips	Potato_Chips	Potato Lays 50gm	502223189	04/May/2015	25

Re: Transpose Help!

ballardw — Thu, 09 Jul 2015 19:23:27 GMT

You should consider importing the data directly to SAS instead of going through Excel. Save a step or two. And if you are going to do this process again you could very well create a more robust import than that provided by Proc Import.

Re: Transpose Help!

SG_Help — Thu, 09 Jul 2015 19:34:50 GMT

Robust. That's all you get.

Re: Transpose Help!

PGStats — Thu, 09 Jul 2015 22:36:47 GMT

Try

proc transpose data=have name=date out=want(rename=col1=price);

by Category Subcategory Class Item_Description Item_ID notsorted;

run;