topic Re: Transpose Question in SAS Programming

Transpose Question

apolitical — Tue, 27 Feb 2018 22:12:33 GMT

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.

Re: Transpose Question

Reeza — Tue, 27 Feb 2018 22:21:20 GMT

Transpose by product year first. Then you'll have a column with the _name_, with 'sales_jan'
Use SCAN() to get the Month, Jan
Use INPUT() to convert that to a SAS date
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.

Re: Transpose Question

Astounding — Tue, 27 Feb 2018 23:01:54 GMT

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;

Re: Transpose Question

apolitical — Tue, 27 Feb 2018 23:24:50 GMT

Works like a beauty. Thank you.

Re: Transpose Question

apolitical — Tue, 27 Feb 2018 23:26:37 GMT

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.