I have reposted:
Please do not respond to this post.
Hi,
I would like calculate a sum for a person using values from a second dataset. Here is the example:
Dataset1
Dave | Agenda 1 |
Kate | Agenda 2 |
Mary | Agenda 3 |
Dataset2
Cost 1 | $100 |
Cost 2 | $200 |
Cost 3 | $300 |
If Agenda 1 then add Cost 1 and Cost 2
If Agenda 2 then add Cost 1 and Cost 3
If Agenda 3 then add Cost 2 and Cost 3
How would I be able to create:
Dave | $300 |
Kate | $400 |
Mary | $500 |
Thank you
Where and how are the rules defined?
If Agenda 1 then add Cost 1 and Cost 2
If Agenda 2 then add Cost 1 and Cost 3
If Agenda 3 then add Cost 2 and Cost 3
Is there a pattern, or do you only have those 3 Agenda's to deal with.
It's easy to code something for this specific situation but it may be more difficult to generalize it.
Yep, I agree with Reeza. It's a bit of a verbose to say the least. Can you please define the full business/tech problem to make it interesting?
This is being used for invoicing. I'm trying to figure out if the sums I'm being billed are correct. This is an education facility. I have to pay for each student's cost. There is a contract which I have to pay for each service a student received. So if Dave received bussing and meals, and in the contract the bussing is $100 per student and meals is $200 per student, I have to figure out how much I owe for Dave. There are many students and there are 21 different costs within the contract.
Do you have a data set that lists hte services and one that list the amounts? If that's the case, you do a join and then add up the total. But that's not currently how your data is structured, it should look like:
Data set 1
Student Service
Dave Bus
Dave Food
Dave Housing
Sandi Bus
Sandi Food
Fred Food
Alice Bus
Data set 2
Service Cost
Bus 100
Food 200
Housing 300
Your Dataset1 only lists one service. However, Dataset1 contains a variable for a list of services each student received.
@hypart wrote:
Your Dataset1 only lists one service. However, Dataset1 contains a variable for a list of services each student received.
Can you clarify your response please? It's not clear which post you're responding to.
SAS can do this easily, and in a number of ways. Here are the missing pieces that you will need to fill in first.
SAS only processes SAS data sets. Either you don't have SAS data sets at this point, or you neglected to tell us the names of the variables. Either way, you will need to fix that first.
SAS doesn't add character strings. Do your costs actually contain dollar signs? We can program around that, but we need to know whether your incoming costs are text strings or not.
I created the datasets manually. The costs do not have any dollar signs. They are numerical. I named the variables a, b, c.
Ok, let's keep this super simple,
data have1;
input var1 $ var2 & $8.;
datalines;
Dave Agenda 1
Kate Agenda 2
Mary Agenda 3
;
data have2;
input col1 & $ col2 dollar5.;
datalines;
Cost 1 $100
Cost 2 $200
Cost 3 $300
;
proc transpose data=have2 out=wide(drop=_NAME_) ;
id col1;
var col2;
run;
Now please use the one to one merge to bring have1 and wide into PDV and write your IF THEN and SUM for each row based on your condition. I am too lazy do that piece. HTH!
data want;
set have1;
if _n_=1 then set wide;
/*Your IF THEN will flow here*/
run;
novinosrin - there are hundreds of names in the first dataset. And there are 21 items in the contract, the second dataset. So they are in two different datasets. I used three items just as an example. A one to one merge doesn't work..
@hypart I think you need to start over and clearly explain your data. Your example above doesn't link to the other data and there has to be a way. I think a better example data is needed and please use the QUOTE button to include the original post so we know what you're responding to.
A merge is what you need and my data does not have one record per name, it has multiple so I have no idea what you're referencing at this point, ergo the suggestion to restart this from scratch.
ok, thanks, I will repost.
While it would work, I assume you don't want to manually add them up and code:
if agenda='Agenda 1' then total_cost=300;
else if agenda='Agenda 2' then total_cost=400;
else if agenda='Agenda 3' then total_cost=500;
etc.
If you want to do that, it's an easy solution. But if you don't want to do that, you will need to construct another data set that defines the agendas. For example:
Agenda Cost1 Cost2 Cost3
1 Y Y N
2 Y N Y
3 N Y Y
This was a reply to your post 12 minutes ago.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.