## SAS base query

Solved
Frequent Contributor
Posts: 78

# SAS base query

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.

Accepted Solutions
Solution
‎02-02-2017 05:39 AM
Super User
Posts: 9,599

## Re: SAS base query

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;

All Replies
Solution
‎02-02-2017 05:39 AM
Super User
Posts: 9,599

## Re: SAS base query

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;

Posts: 1,837

## Re: SAS base query

[ Edited ]

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;

☑ This topic is solved.