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-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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