BookmarkSubscribeRSS Feed
hypart
Calcite | Level 5

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

 

15 REPLIES 15
Reeza
Super User

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.

novinosrin
Tourmaline | Level 20

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?

hypart
Calcite | Level 5

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.

 

Reeza
Super User

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

 

 

hypart
Calcite | Level 5

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

Reeza
Super User

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

Astounding
PROC Star

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.

hypart
Calcite | Level 5

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

novinosrin
Tourmaline | Level 20

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;

 

 

hypart
Calcite | Level 5

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

Reeza
Super User

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

hypart
Calcite | Level 5

ok, thanks, I will repost.

Astounding
PROC Star

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

 

 

hypart
Calcite | Level 5

This was a reply to your post 12 minutes ago.  

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 15 replies
  • 2147 views
  • 0 likes
  • 5 in conversation