Ok, so the answer to this question may very well have been poseed here or on the intarwebz somewhere, but I can't find it, so I'm submitting it.
A lot of the programs on which I work use SAS to connect to a SQL Server db and retrieve data. We do not have a SAS server. We just use the local. As such, we tend to write proc sql blocks to run as much query logic on SQL Server as possible and then pull in the smallest a result set possible to the local SAS workspace for post processing that utilizes SAS specific features.
Often we have a known set of information that we can send via the passthrough query to improve performance and efficiency. Things like lists of customer ids for example are common. For such lists we've used macro variables in the passthrough, but frequently, this does not work because the contents being fed to the macro variables might exceed the macro variable character limit. That results in truncation and broken programs.
I'm sure there is a better way to do this and have been searching fo a long time to find it. Thus far I've come up empty.
I hope there are some among you that have run into this before and might be able to shed some light.
Thanks!!
If the information you pass to the SQL server exists in SAS it may be better to instead of a macro varaible with a LIst to 1) build a SAS data set, 2) sent that to the SQL server and 3) then execute a pass-through query as a Join on that data set.
If the information you pass to the SQL server exists in SAS it may be better to instead of a macro varaible with a LIst to 1) build a SAS data set, 2) sent that to the SQL server and 3) then execute a pass-through query as a Join on that data set.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.