Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- SAS Procedures
- /
- Using Proc SQL to sum based on three conditions

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

09-30-2013 03:14 PM

Let me start by saying I'm new. Very new.

I have three data sets:

BEGINNING_BALANCE has account_number, date, available_Balance, and currency_type.

SMALL_FIN has account_number, date, currency_type, relation_number, and transaction_amt.

ELIGIBLE_ACCOUNT has account_number, date, and relation_number.

I need to know if an eligible transaction occurred without a fee. In order to solve for this, I need to take the available_balance from the BEGINNING_BALANCE table and then add all eligible transactions to date listed on the SMALL_FIN table to get the actual amount available when the transaction occurred. This is further complicated because it's possible for one account to have multiple transactions on the same day. So i have to make sure that my results show the total amount available on the account for each unique account number, date, and relation number combination. I need to make sure that the relation numbers from ELIGIBLE_ACCOUNT match SMALL_FIN.

I know logically what needs to happen but when i try to work it out in SQL i get awful results. I've gotten far enough to create these three tables from larger disparate data sources but now i'm stuck. Any help would be really appreciated. Please let me know if i need to clarify anything or if I've left out details you may need. Again, thanks for any help, I really appreciate it. C

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

09-30-2013 03:24 PM

Mike,

Can you post (very small) example of your data and expected result as data. De-identify it where required and try and make sure the data matches the output.

Sometimes its easier to work backwards, from the smaller table to the bigger ones or it could be easier in a datastep, but I'm having a hard time understanding at the moment.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

09-30-2013 03:36 PM

Sure . . .

BEGINNING_BALANCE

account_Number Date Available_Balance Currency_type

1234567 09SEP2013 0.00 DL

2345678 09SEP2013 0.00 DL

SMALL_FIN

Account_Number Date trans_amt Currency_type Relation_Num

2345678 11SEP2013 100.00 DL 1

2345678 11SEP2013 0.00 DL 2

ELIGIBLE_Account

Account_Number Date Relation_Num

2345678 11SEP2013 1

2345678 11SEP2013 2

So given the above data, i'd want my results to look like this:

Account Number date Currency_type Relation Number Net_Balance

2345678 11SEP2013 DL 2 0.00

Since account 1234567 isn't on the ELIGIBLE_ACCOUNT table it isn't included in the results. Since relation 1 on account 2345678 has a trans_amt > 0.00 it isn't included but relation 2 is. Does that help?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

09-30-2013 03:48 PM

Why do need the beginning balance table?

Does this get you closer?

proc sql;

create table want as

select e.*, s.currency_type, s.trans_amt

from elegible_account e

join small_fin s

on e.account_number=s.account_number

and e.relation_num=s.relation_num

and s.trans_amt=0;

quit;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

09-30-2013 03:54 PM

I need to solve for the amount available on the account. We only record the balance in a snapshot . . . The BEGINNING BALANCE table gives me a moment in time where i know the account balance. If someone starts with 500 on their account a transaction of 0 is fine. Without the Beginning balance i'd see that as a free transaction.

I'll try the query you suggested.

Thanks Reeza.