topic Re: Approach for retrieving values in SAS Programming

Approach for retrieving values

JasonNC — Fri, 24 Mar 2017 16:22:47 GMT

Hi ,

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 ','
from Anlsys;
quit;

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
on(b.FKey =c.FKey)
where a.Account_Number IN (&Accnt)
);
disconnect from odbc;
quit;

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

Re: Approach for retrieving values

ArtC — Sat, 25 Mar 2017 00:25:56 GMT

The macro variable will hold 64K characters, so depending on the length of your account number (+1 for the comma) you can get a good approximation of how many will fit.

if you can push the WORK.ANLSYS up to the data base you can add it to your join to do the elimination.

Re: Approach for retrieving values

Patrick — Sat, 25 Mar 2017 13:25:46 GMT

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

https://support.sas.com/documentation/cdl/en/acreldb/69580/HTML/default/viewer.htm#n0irpkyp22l7vzn1il9lx6f4wmx9.htm

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.

https://support.sas.com/documentation/cdl/en/acreldb/69580/HTML/default/viewer.htm#n0jg0sozl17mjyn1woelrrr76266.htm

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.