BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
scb
Obsidian | Level 7 scb
Obsidian | Level 7

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.

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

2 REPLIES 2
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Garnet | Level 18

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;

 

sas-innovate-white.png

Register Today!

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.

Register now!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

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