DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Contributor
Posts: 20
Accepted Solution

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

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


Accepted Solutions
Solution
Sunday
Super User
Posts: 11,343

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

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.

 

 

View solution in original post


All Replies
Solution
Sunday
Super User
Posts: 11,343

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

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.

 

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 1 reply
  • 152 views
  • 0 likes
  • 2 in conversation