Transpose Help!

Accepted Solution Solved
Reply
Contributor
Posts: 29
Accepted Solution

Transpose Help!

Hi,

i have data set has shown below:

CategorySubcategoryClassItem_Description
Item_ID30/March/20156/April/201513/April/2015

20/April/2015

27/April/2015

4/May/2015

SnacksChipsPotato_ChipsPotato Lays 50gm502223189232624222325
SnacksChipsChilly_ChipsChilly Lays 50gm621452221262425212224

    

Required O/P:

CategorySubcategoryClassItem_Description
Item_ID

Date

Price

SnacksChips

Potato_Chips

Potato Lays 50gm

50222318930/March/2015

23

SnacksChips

Potato_Chips

Potato Lays 50gm

5022231896/April/201526
SnacksChips

Potato_Chips

Potato Lays 50gm

50222318913/April/201524
SnacksChips

Potato_Chips

Potato Lays 50gm

50222318920/April/201522
SnacksChips

Potato_Chips

Potato Lays 50gm

50222318927/April/201523
SnacksChips

Potato_Chips

Potato Lays 50gm

502223189

4/May/2015

25
SnacksChips

Chilly_Chips

Chilly Lays 50gm

62145222130/March/201526
SnacksChips

Chilly_Chips

Chilly Lays 50gm

6214522216/April/201524
SnacksChips

Chilly_Chips

Chilly Lays 50gm

62145222113/April/201525
SnacksChips

Chilly_Chips

Chilly Lays 50gm

62145222120/April/201521
SnacksChips

Chilly_Chips

Chilly Lays 50gm

62145222127/April/201522
SnacksChips

Chilly_Chips

Chilly Lays 50gm

6214522214/May/201524

How to transpose as shown above?

Thanks,

Ganesh K


Accepted Solutions
Solution
‎07-09-2015 02:12 PM
New Contributor
Posts: 2

Re: Transpose Help!

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;

SnacksChipsChilly_ChipsChilly Lays 50gm62145222130/March/201526
SnacksChipsChilly_ChipsChilly Lays 50gm62145222106/April/201524
SnacksChipsChilly_ChipsChilly Lays 50gm62145222113/April/201525
SnacksChipsChilly_ChipsChilly Lays 50gm62145222120/April/201521
SnacksChipsChilly_ChipsChilly Lays 50gm62145222127/April/201522
SnacksChipsChilly_ChipsChilly Lays 50gm62145222104/May/201524
SnacksChipsPotato_ChipsPotato Lays 50gm50222318930/March/201523
SnacksChipsPotato_ChipsPotato Lays 50gm50222318906/April/201526
SnacksChipsPotato_ChipsPotato Lays 50gm50222318913/April/201524
SnacksChipsPotato_ChipsPotato Lays 50gm50222318920/April/201522
SnacksChipsPotato_ChipsPotato Lays 50gm50222318927/April/201523
SnacksChipsPotato_ChipsPotato Lays 50gm50222318904/May/201525

View solution in original post


All Replies
Respected Advisor
Posts: 4,820

Re: Transpose Help!

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

PG
Solution
‎07-09-2015 02:12 PM
New Contributor
Posts: 2

Re: Transpose Help!

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;

SnacksChipsChilly_ChipsChilly Lays 50gm62145222130/March/201526
SnacksChipsChilly_ChipsChilly Lays 50gm62145222106/April/201524
SnacksChipsChilly_ChipsChilly Lays 50gm62145222113/April/201525
SnacksChipsChilly_ChipsChilly Lays 50gm62145222120/April/201521
SnacksChipsChilly_ChipsChilly Lays 50gm62145222127/April/201522
SnacksChipsChilly_ChipsChilly Lays 50gm62145222104/May/201524
SnacksChipsPotato_ChipsPotato Lays 50gm50222318930/March/201523
SnacksChipsPotato_ChipsPotato Lays 50gm50222318906/April/201526
SnacksChipsPotato_ChipsPotato Lays 50gm50222318913/April/201524
SnacksChipsPotato_ChipsPotato Lays 50gm50222318920/April/201522
SnacksChipsPotato_ChipsPotato Lays 50gm50222318927/April/201523
SnacksChipsPotato_ChipsPotato Lays 50gm50222318904/May/201525
Super User
Posts: 11,134

Re: Transpose Help!

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.

New Contributor
Posts: 2

Re: Transpose Help!

Robust.  That's all you get.

Respected Advisor
Posts: 4,820

Re: Transpose Help!

Try

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

by Category Subcategory Class Item_Description Item_ID notsorted;

run;

PG

PG
🔒 This topic is solved and locked.

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

Discussion stats
  • 5 replies
  • 357 views
  • 3 likes
  • 4 in conversation