DATA Step, Macro, Functions and more

SAS base query

Accepted Solution Solved
Reply
Contributor scb
Contributor
Posts: 69
Accepted Solution

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
Super User
Posts: 7,942

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;

View solution in original post


All Replies
Solution
‎02-02-2017 05:39 AM
Super User
Super User
Posts: 7,942

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;

Trusted Advisor
Posts: 1,558

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 122 views
  • 0 likes
  • 3 in conversation