BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
H4xc1ty
Fluorite | Level 6

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

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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

1 REPLY 1
ballardw
Super User

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.

 

 

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

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