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.
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)
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
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)
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)
Thanks. Yes, perhaps parenthesis around the subqueries will be needed. I cannot test it as i dont have access to base sas currently
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.