I have the below dataset. May I know how to create the following result from the test dataset? I tried to use proc transpose but I just could not get it.
For prod ABC; Amt will be equally divided to 5 months, XYZ will be 4 months.
Result:
Case Prod Month1 Month2 Month3 Month4 Month5 Total
ABC123 ABC 200 200 200 200 200 1000
ABC124 ABC 400 400 400 400 400 2000
XYZ123 XYZ 200 200 200 200 0 800
XYZ124 XYZ 50 50 50 50 0 200
DATA TEST;
INPUT CASE $ PROD $ AMT;
DATALINES;
ABC123 ABC 1000
ABC124 ABC 2000
XYZ123 XYZ 800
XYZ124 XYZ 200
;
RUN;
Thank you very much.
First you need to modify your base dataset to include a variable for month, there is no way of knowing from the test data you post that, month1 has 200. What if month 1 has 1000 and the rest 0?
As for the actual code:
data want; set have; array month{5} 8; do i=1 to 5; month{i}=total / 5); end; run;
Or whatever your logic is to work out what each month is just replace the =total/5;
First you need to modify your base dataset to include a variable for month, there is no way of knowing from the test data you post that, month1 has 200. What if month 1 has 1000 and the rest 0?
As for the actual code:
data want; set have; array month{5} 8; do i=1 to 5; month{i}=total / 5); end; run;
Or whatever your logic is to work out what each month is just replace the =total/5;
your code to create test data:
DATA TEST;
INPUT CASE $ PROD $ AMT;
DATALINES;
ABC123 ABC 1000
ABC124 ABC 2000
XYZ123 XYZ 800
XYZ124 XYZ 200
;
RUN;
code you want:
data want;
set test(rename = (amt = total));
array mx month1-month5;
if prod = 'ABC' then monthly = total/5; else
if prod = 'XYZ' then monthly = total/4;
do i=1 to 5;
if i < 5 then mx(i) = monthly; else
if prod = 'ABC' then mx(5) = monthly; else
if prod = 'XYZ' then mx(5) = 0;
end;
drop i monthly;
run;
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
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.
Ready to level-up your skills? Choose your own adventure.