topic SAS base query in SAS Programming

SAS base query

scb — Mon, 30 Jan 2017 14:13:57 GMT

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.

Re: SAS base query

RW9 — Mon, 30 Jan 2017 14:27:22 GMT

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;

Re: SAS base query

Shmuel — Mon, 30 Jan 2017 15:06:11 GMT

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;