summing across 4 datasets using proc sql

[devsas]

Good evening everyone! I have multiple tables named in the format - bankX_YYYYMM

Where X is name of bank (1,2.3 etc) and YYYY is year and MM is month. Each table has following variables

Account_no, registered(indicates if the account is registered), num_trans (number of transactions made during that month), spend (total spend during the time period)

1. I want to have a result which gives me total number of transactions and total spend for "Bank1" during the 4th quarter of 2009.

    My code-

     Proc sql;

Create table test as select sum(bank1_200909.num_trans, bank1_200910.num_trans, bank1_200911.num_trans, bank1_200912.num_trans) as total_transactions, sum(bank1_200909.spend, bank1_200910.spend, bank1_200911.spend, bank1_200912.spend) as total spend from bank1_200909, bank1_200910, bank1_200911, bank1_200912;
quit;

Is this correct?

2. What Proc sql query i can write to display the total number of transactions and total spend at "Bank1" and "Bank2", broken out by registered vs non-registered accounts, during jan 2010?

Thanks in advance. Please only PROC SQL solution, no data step or macros.

[MarkWik]

Can you please provide of data sample of your tables so that the community can work on and test their code responses. It only makes it so much more easier

[PGStats]

1. I don't think the fourth quarter includes september.... But more importantly, your query creates the cartesian product of the tables. That's not what you want. You simply need to concatenate the tables. Something like this:

Proc sql;
Create table test as 
select 
    sum(num_trans) as total_transactions, 
    sum(spend) as total_spend 
from 
select num_trans, spend from bank1_200910
union all
select num_trans, spend from bank1_200911
union all
select num_trans, spend from bank1_200912;
quit;

(untested)

[devsas]

Thanks PGStats. Yes, you are correct sept is not needed. Any idea on second part of my question? My guess is adding group by statement at the end of your query should suffice?

[PGStats · Accepted solution]

Something like this, perhaps:

Proc sql;
Create table test2 as 
select 
    registered,
    sum(num_trans) as total_transactions, 
    sum(spend) as total spend 
from 
select registered, num_trans, spend from bank1_201001
union all
select registered, num_trans, spend from bank2_201001
group by registered;
quit;

(Untested, maybe you need parentheses around the subqueries)

[devsas]

Thanks. Yes, perhaps parenthesis around the subqueries will be needed. I cannot test it as i dont have access to base sas currently