08-29-2017 01:44 PM
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:
(there are many students, have not listed them all)
Item Cost $
(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
I would like to create this output:
etc...for all students
I hope this clear, if not please let me know.
08-29-2017 01:51 PM
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.
08-29-2017 02:00 PM
Here's a way to approach it.
proc transpose data=contract out=costs (drop=_name_);
You can take a look at the data set COSTS at that point, to get a picture of what is happening. Then:
if _n_=1 then set costs;
when ('Plan1') TotalSum=Bussing + Tutoring;
when ('Plan2') TotalSum=Clothes + Books + Meals;
when ('Plan3') TotalSum=Insurance;
keep student TotalSum;
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.
08-29-2017 02:07 PM
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;
output out=final (drop=_ sum=;
08-29-2017 02:11 PM
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.