Use SAS temp data set variable(column) in a query pulling data from ODBC source

Reply
New Contributor
Posts: 3

Use SAS temp data set variable(column) in a query pulling data from ODBC source

Hi there,

I just got SAS installed and still very new to it. I want to query a table (connected via ODBC source) and use a SAS temporary data set variable to limit the records that would be returned by the query. However, i am getting the following ERROR: CLI describe error: [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'work.total_routed'. : [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.


I have attached my code below for your reference. I have added a comment to my code = Getting error here to indicate the specific part of the code that i am unable to run.

I may be understanding this wrong but i see no purpose of creating a data set if i cant use it to manipulate other queries with tables connected via ODBC in SAS.

Any help with this will be greatly appreciated.

Thanks,

Ani

Attachment
Super User
Posts: 3,106

Re: Use SAS temp data set variable(column) in a query pulling data from ODBC source

Your ODBC queries are executing on an external database. The SAS temporary dataset exists on the computer where your SAS session is running. You get the error because the SAS dataset isn't in your external database.

You have two choices:

Extract all your ODBC data into your SAS environment and then select based on your SAS table or....

Upload your SAS temporary dataset into the external database so that they both co-exist in the same location.

There is a third option which works if the selection criteria from your SAS table are fairly short and can be built into a WHERE clause which can be passed into the ODBC query - perhaps a few hundred values to select on....


Super User
Posts: 5,257

Re: Use SAS temp data set variable(column) in a query pulling data from ODBC source

You might want to investigate in the DBKEY= option. To be to use that, you need to use SQL implicit-pass-thru (as opposed to explicit using "... FROM CONNECTION TO" and "EXECUTE .... BY").

Haven't studied your SQL in detail, but it seems that it isn't necessary to use explicit pass thru.

Data never sleeps
Ask a Question
Discussion stats
  • 2 replies
  • 738 views
  • 0 likes
  • 3 in conversation