BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Ganeshk
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
SG_Help
Calcite | Level 5

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

5 REPLIES 5
PGStats
Opal | Level 21

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

PG
SG_Help
Calcite | Level 5

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
ballardw
Super User

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.

SG_Help
Calcite | Level 5

Robust.  That's all you get.

PGStats
Opal | Level 21

Try

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

by Category Subcategory Class Item_Description Item_ID notsorted;

run;

PG

PG

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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