DATA Step, Macro, Functions and more

Transpose Question

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 88
Accepted Solution

Transpose Question

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.


Accepted Solutions
Solution
‎02-27-2018 06:24 PM
Super User
Posts: 6,632

Re: Transpose Question

Posted in reply to apolitical

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


All Replies
Super User
Posts: 23,295

Re: Transpose Question

Posted in reply to apolitical
  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.

Frequent Contributor
Posts: 88

Re: Transpose Question

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.
Solution
‎02-27-2018 06:24 PM
Super User
Posts: 6,632

Re: Transpose Question

Posted in reply to apolitical

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;

 

Frequent Contributor
Posts: 88

Re: Transpose Question

Posted in reply to Astounding
Works like a beauty. Thank you.
☑ This topic is solved.

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

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