DATA Step, Macro, Functions and more

summing across 4 datasets using proc sql

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 121
Accepted Solution

summing across 4 datasets using proc sql

[ Edited ]

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.

 


Accepted Solutions
Solution
‎03-13-2018 10:22 AM
Esteemed Advisor
Posts: 5,624

Re: summing across 4 datasets using proc sql

[ Edited ]

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)

PG

View solution in original post


All Replies
Frequent Contributor
Posts: 105

Re: summing across 4 datasets using proc sql

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

Esteemed Advisor
Posts: 5,624

Re: summing across 4 datasets using proc sql

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)

 

PG
Frequent Contributor
Posts: 121

Re: summing across 4 datasets using proc sql

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?
Solution
‎03-13-2018 10:22 AM
Esteemed Advisor
Posts: 5,624

Re: summing across 4 datasets using proc sql

[ Edited ]

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)

PG
Frequent Contributor
Posts: 121

Re: summing across 4 datasets using proc sql

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 127 views
  • 0 likes
  • 3 in conversation