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

If I have a data with these variables:

 

product   year   sales_jan   sales_feb   sales_mar....sales_nov   sales_dec

 

How can I transpose it to something like this?

 

product   year   month   sales

 

Month is a number (1-12).Thank you.

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

I would use a DATA step, and create MONTH as a numeric variable:

 

data want;

set have;

array monsales {12} sales_jan sales_feb sales_mar sales_apr sales_may sales_jun

sales_jul sales_aug sales_sep sales_oct sales_nov sales_dec;

do month = 1 to 12;

   sales = monsales{month};

   output;

end;

drop sales_: ;

run;

 

View solution in original post

4 REPLIES 4
Reeza
Super User
  1. Transpose by product year first. Then you'll have a column with the _name_, with 'sales_jan'
  2. Use SCAN() to get the Month, Jan
  3. Use INPUT() to convert that to a SAS date
  4. USE MONTH() to calculate the month

Or replace steps 2-4 with a set of 13 IF/THEN statements, one for each month and one to catch errors.

apolitical
Obsidian | Level 7
Admittedly I don't know much about proc transpose. Because the month are in letters not numbers in the variable names, my attempt results in error stating " QTY_JAN does not have a numeric suffix".
Thank you nonetheless.
Astounding
PROC Star

I would use a DATA step, and create MONTH as a numeric variable:

 

data want;

set have;

array monsales {12} sales_jan sales_feb sales_mar sales_apr sales_may sales_jun

sales_jul sales_aug sales_sep sales_oct sales_nov sales_dec;

do month = 1 to 12;

   sales = monsales{month};

   output;

end;

drop sales_: ;

run;

 

apolitical
Obsidian | Level 7
Works like a beauty. Thank you.

hackathon24-white-horiz.png

The 2025 SAS Hackathon has begun!

It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.

Latest Updates

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • 1528 views
  • 2 likes
  • 3 in conversation