How do I send dynamic "where" criteria via passthrough query

[H4xc1ty]

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!!

[ballardw · Accepted solution]

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.