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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.