topic Re: Sum fields from different datasets in SAS Programming

Sum fields from different datasets

hypart — Tue, 29 Aug 2017 17:46:04 GMT

I have reposted:

https://communities.sas.com/t5/Base-SAS-Programming/How-to-add-and-create-a-Total-Sum-from-values-in-a-second/m-p/391606#M94100

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

Re: Sum fields from different datasets

Reeza — Tue, 29 Aug 2017 16:18:51 GMT

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.

Re: Sum fields from different datasets

novinosrin — Tue, 29 Aug 2017 16:20:44 GMT

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?

Re: Sum fields from different datasets

Astounding — Tue, 29 Aug 2017 16:21:31 GMT

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.

Re: Sum fields from different datasets

hypart — Tue, 29 Aug 2017 16:30:45 GMT

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.

Re: Sum fields from different datasets

Reeza — Tue, 29 Aug 2017 16:32:53 GMT

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

Re: Sum fields from different datasets

hypart — Tue, 29 Aug 2017 16:35:15 GMT

I created the datasets manually. The costs do not have any dollar signs. They are numerical. I named the variables a, b, c.

Re: Sum fields from different datasets

hypart — Tue, 29 Aug 2017 16:38:32 GMT

Your Dataset1 only lists one service. However, Dataset1 contains a variable for a list of services each student received.

Re: Sum fields from different datasets

novinosrin — Tue, 29 Aug 2017 16:46:12 GMT

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;

Re: Sum fields from different datasets

Reeza — Tue, 29 Aug 2017 16:44:29 GMT

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

Re: Sum fields from different datasets

Astounding — Tue, 29 Aug 2017 16:46:03 GMT

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

Re: Sum fields from different datasets

hypart — Tue, 29 Aug 2017 16:47:08 GMT

This was a reply to your post 12 minutes ago.

Re: Sum fields from different datasets

hypart — Tue, 29 Aug 2017 16:58:42 GMT

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

Re: Sum fields from different datasets

Reeza — Tue, 29 Aug 2017 17:04:42 GMT

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

Re: Sum fields from different datasets

hypart — Tue, 29 Aug 2017 17:09:19 GMT

ok, thanks, I will repost.

Re: Sum fields from different datasets

ballardw — Tue, 29 Aug 2017 17:20:20 GMT

@hypart wrote:

This was a reply to your post 12 minutes ago.

When it is unclear which post responding to it is best to start with the post you want to respond to and select the reply button. Then in the message window click on the Quote button at the upper right to bring in the text. Feel free to delete parts you don't need but this way we have a chance of knowing which is being discussed.

Other is to copy the text from anothe message, paste it into the window and quote the text.