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
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 |
You are asking for trouble with variable names like 30/April/2015. Are these column headers your variable names or labels? - PG
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 |
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.
Robust. That's all you get.
Try
proc transpose data=have name=date out=want(rename=col1=price);
by Category Subcategory Class Item_Description Item_ID notsorted;
run;
PG
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.
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.