Transpose Question

[apolitical]

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.

[Reeza]

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]

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 · Accepted solution]

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]

Works like a beauty. Thank you.