How to add and create a Total Sum from values in a second dataset

[hypart]

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

Dave	Plan1
Kate	Plan2
Mary	Plan3

Emma	Plan4
Noah	Plan5
James	Plan6

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

Contract.sas7bdat:

Item          Cost  $     

Bussing	100
Meals	150
Books	200

Clothes	250
Tutoring	300
Insurance	350

(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.

[Reeza]

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

[hypart]

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]

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]

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]

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]

Works!  Thanks so much everyone!