03-24-2017 12:22 PM
Currently I am retrieving values from a database using macro variable as i have to do look up for the account values .i am doing below approach as it makes processing faster.
PROC SQL NOPRINT;
Select Account_Num into :Accnt Separated by ','
proc sql noprint;
connect to odbc(dsn=mart);
create table Lookup as
select * from connection to odbc(select a.Account_Number,c.Score
from mart.dbo.accounts a left join mart.dbo.ACCOUNT_FACTS b
on ( a.actkey = b.actkey)
left join mart.dbo.Score_yr c
where a.Account_Number IN (&Accnt)
disconnect from odbc;
I don't have any checks on macro variable length.I am exposing to risk of exceeding macro variable length but i know the accounts list might not be that much but still little concern
Is there any other better approach to retrieve efficiently instead of pulling the whole table into sas
03-24-2017 08:25 PM
03-25-2017 09:24 AM - edited 03-25-2017 09:25 AM
As far as I know you've got basically two options if you want to influence how and where heterogenous joins are executed.
1. You load your smaller table into the DB with the larger table. You can use a temporary table for this
2. You pack the key values of the smaller tables into a where clause for a SQL sent to the DB with the larger table. That's what you're currently doing.
In a company I've worked many years ago we've actually implemented a "sqlpump" macro which did exactly what you're doing now: Pack the keys into a macro variable and then send the SQL to the DB for execution; and we've implemented in a way that this executed in multiple iterations when there were too many key values and then appended the result back in SAS.
BTW: I believe some DBMS have a restriction of 32KB for SQL's so you want to limit your macro variable to something a bit lower than 32KB per call.
I believe that there is now a SAS dataset option DBMASTER which triggers SAS/Access to generate such code when using implicit SQL.
When using implicit SQL (so not pass-through) then also use the following options:
options sastrace=',,,d' sastraceloc=saslog nostsuffix;
This will show you in the log what SQL the SAS/Access engine actually sends to the database for execution.