DATA Step, Macro, Functions and more

Sum fields from different datasets

Reply
Occasional Contributor
Posts: 13

Sum fields from different datasets

[ Edited ]

I have reposted:

https://communities.sas.com/t5/Base-SAS-Programming/How-to-add-and-create-a-Total-Sum-from-values-in...

 

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

DaveAgenda 1
KateAgenda 2
MaryAgenda 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

 

Super User
Posts: 19,878

Re: Sum fields from different datasets

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.

PROC Star
Posts: 283

Re: Sum fields from different datasets

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?

Occasional Contributor
Posts: 13

Re: Sum fields from different datasets

Posted in reply to novinosrin

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.

 

Super User
Posts: 19,878

Re: Sum fields from different datasets

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

 

 

Occasional Contributor
Posts: 13

Re: Sum fields from different datasets

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

Super User
Posts: 19,878

Re: Sum fields from different datasets


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. 

Super User
Posts: 5,518

Re: Sum fields from different datasets

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.

Occasional Contributor
Posts: 13

Re: Sum fields from different datasets

Posted in reply to Astounding

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

PROC Star
Posts: 283

Re: Sum fields from different datasets

[ Edited ]

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;

 

 

Occasional Contributor
Posts: 13

Re: Sum fields from different datasets

Posted in reply to novinosrin

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

Super User
Posts: 19,878

Re: Sum fields from different datasets

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

Occasional Contributor
Posts: 13

Re: Sum fields from different datasets

ok, thanks, I will repost.

Super User
Posts: 5,518

Re: Sum fields from different datasets

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

 

 

Occasional Contributor
Posts: 13

Re: Sum fields from different datasets

Posted in reply to Astounding

This was a reply to your post 12 minutes ago.  

Ask a Question
Discussion stats
  • 15 replies
  • 155 views
  • 0 likes
  • 5 in conversation