SAS base query

[scb]

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.

[RW9 · Accepted solution]

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;

[Shmuel]

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;