BookmarkSubscribeRSS Feed
hypart
Calcite | Level 5

Hi,

 

I would like calculate a total sum for each student based on the program they are on by adding values from a second dataset.  Here is the example:

 

invoice.sas7bdat:

Student  Program

DavePlan1
KatePlan2
MaryPlan3
EmmaPlan4
NoahPlan5
JamesPlan6

 (there are many students, have not listed them all)

 

 

Contract.sas7bdat:

Item          Cost  $     

Bussing100
Meals150
Books200
Clothes250
Tutoring300
Insurance350

 

(there are 21 items, have not listed them all)

 

I would like to program:

 

If a student is on Plan1, then the TotalSum=Bussing+Tutoring

If a student is on Plan2, then the TotalSum=Clothes+Books+Meals

If a studen is on Plan3, then the TotalSum=Insurance

etc......

 

I would like to create this output:

Student TotalSum

Dave$400
Kate$600
Mary$350

etc...for all students

 

I hope this clear, if not please let me know.

6 REPLIES 6
Reeza
Super User

Can you also provide the plan definitions. You still don't have a way to link these files. 

hypart
Calcite | Level 5

The plan definitions is in the section I would like programmed.  I'm sorry, I don't know how to link the two files.  I have two datasets, invoice.sas7bdat and contract.sas7bdat.  That's as far as I've gotten.

 

Astounding
PROC Star

Here's a way to approach it.

 

proc transpose data=contract out=costs (drop=_name_);

var cost;

id item;

run;

 

You can take a look at the data set COSTS at that point, to get a picture of what is happening.  Then:

 

data want;

if _n_=1 then set costs;

set invoice;

select (program);

   when ('Plan1') TotalSum=Bussing + Tutoring;

   when ('Plan2') TotalSum=Clothes + Books + Meals;

   when ('Plan3') TotalSum=Insurance;

   otherwise;

end;

keep student TotalSum;

run;

 

You'll need to add to the WHEN statements, to define all the plans.  And the spelling of the programs ("Plan1", "Plan2") must exactly match the spelling in the INVOICE data set.

ballardw
Super User

This may give you start:

data contract;
   informat item $15.;
   input item $ cost;
datalines;
Bussing 100 
Meals 150 
Books 200 
Clothes 250 
Tutoring 300 
Insurance 350 
;
run;

proc transpose data=contract out=contrans (drop=_name_);
id item;
var cost;
run;

data contractcosts;
   set contrans;
   Plan1 = sum(bussing,tutoring);
   Plan2 = sum(Clothes,Books,Meals);
   Plan3 = Insurance;
run;
proc transpose data=contractcosts out=costtrans;
var plan: ;
run;

Data invoice;
   input student $ Program $;
datalines;
Dave Plan1 
Kate Plan2 
Mary Plan3 
;
run;

proc sql;
   create table want as
   select invoice.student, costtrans.col1 as total
   from invoice left join costtrans
     on upcase(invoice.program)=upcase(costtrans._name_);
quit;

Since you do not provide any example of what Plans 4, 5, 6 etc may look like I did not attempt them. Add them into the contractcosts following the obvious pattern.

 

Note how to provide data as data step.

This will not work for any student that has 2 or more "plans" as shown. If you have that case then the "want" set would need to be summed one more time with something like:

Proc summary data=want nway;

   class student;

    var total;

    output out=final (drop=_:) sum=;

run;

Reeza
Super User

I think you need to create the PLAN data set - manually if required.

How do you know what PLAN1, PLAN2 correspond to? If it's in your head you can create the data set by writing it out. 

Or IF/THEN but then any changes require changes to your IF/THEN statement rather than updating your PLAN dataset and running the rest of your code.

 

Here's an example of how to create it:

 

data plan;
input plan $ service $;
cards;
Plan1 Bus
Plan1 Food
Plan1 House
Plan2 Bus
Plan2 Food
Plan3 Bus
Plan4 Food
Plan5 House
;
run;

Once you build a dataset like that you can then do the merges properly.

 

Note that IF/THEN works fine, just that this approach will make it easier on you if you ever need to repeat this or if you need to extend it. Obviously this is my opinion only.

hypart
Calcite | Level 5

Works!  Thanks so much everyone!

 

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
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
  • 6 replies
  • 1384 views
  • 2 likes
  • 4 in conversation