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.

 

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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