Calcite | Level 5

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

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.

6 REPLIES 6
Super User

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

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

Calcite | Level 5

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

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.

PROC Star

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

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.

Super User

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

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;

Super User

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

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.

Calcite | Level 5

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

Works!  Thanks so much everyone!

Discussion stats
• 6 replies
• 1425 views
• 2 likes
• 4 in conversation