topic Re: Can you use a local table in a passthrough? in SAS Programming

Can you use a local table in a passthrough?

lsirakos — Fri, 07 Sep 2012 15:51:47 GMT

Like the title states, is it possible to use a local dataset in the work directory in a passthrough? I was not sure if you could use it as a temp table in someway. I have to use a passthrough to query data and would like to use a dataset created in SAS with the query. It is possible for me to pull in the data I need using the passthrough and then use a proc sql statement to utilize the local dataset but the process would be much more efficient if it was possible to just use the data in the passthrough.

Thanks in advance

Re: Can you use a local table in a passthrough?

TomKari — Fri, 07 Sep 2012 16:16:22 GMT

I don't think so. If I understand it correctly, passthrough will send the code directly to a (non-SAS) database server, which will execute the code. I can't see how your database server would know what to do with a SAS dataset.

You are absolutely correct that it may be much more efficient to reduce the size of the returned data in the database server. If your SAS temp dataset is just a list of join values that you want to select from your big table, consider putting them into a macro variable, which you can use as WHERE var IN(&macrovariable). This might quite fast.

I have the feeling there's a SAS/Access option that will tell SAS to do this automatically, but I can't find it offhand. (Anybody out there know the name of this?)

Another option is to load your SAS temp dataset to the database, and then you can implement the join as a passthrough.

Tom

Re: Can you use a local table in a passthrough?

lsirakos — Fri, 07 Sep 2012 16:22:17 GMT

Thanks Tom, that is what I figured. I didn't know if you were using a passthrough to connect to a SQL Server if it would be possible to pass the local dataset in as a temporary table or something along those lines. The problem with loading the data into the database is I only have read access.

Thanks

Re: Can you use a local table in a passthrough?

JerryH — Mon, 10 Sep 2012 20:07:47 GMT

Tom is correct. In point of fact, since the pass-through facility sends raw, or native, SQL to the data base nothing that is strictly SAS will work. I do not have the capability at the moment to do any testing so I don't know which would be the most efficient but you have three choices: (1) Run the query and then the data step to join your data sets, (2) do as Tom suggested (if possible) and put the SAS data set in a macro variable and use that in your query and (3) don't use the pass-through facility - that would enable you to call from both the Oracle table and the existing SAS data set - SAS would determine which part(s) of the query to pass to the data base. I am guessing (3) might be the most efficient in the long run but do not know.