topic Re: summing across 4 datasets using proc sql in SAS Programming

summing across 4 datasets using proc sql

devsas — Tue, 13 Mar 2018 03:29:35 GMT

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.

Re: summing across 4 datasets using proc sql

MarkWik — Tue, 13 Mar 2018 03:36:20 GMT

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

Re: summing across 4 datasets using proc sql

PGStats — Tue, 13 Mar 2018 04:07:08 GMT

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)

Re: summing across 4 datasets using proc sql

devsas — Tue, 13 Mar 2018 04:14:25 GMT

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?

Re: summing across 4 datasets using proc sql

PGStats — Tue, 13 Mar 2018 04:24:15 GMT

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)

Re: summing across 4 datasets using proc sql

devsas — Tue, 13 Mar 2018 14:22:23 GMT

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