BookmarkSubscribeRSS Feed
LaurieF
Barite | Level 11

I've been trying to get around an issue when I upload data through SAS to an ODBC-connected DBMS. I've tried all sorts of variations, but the issue won't go away. Well, I say issue, but I don't know what the issue is!

 

I'm creating a table via an SQL execute statement, then using a SAS-initiated insert statement to load into it. The message is:

NOTE: The SQL statement cannot be executed directly against the database because it involves heterogeneous data sources.

 

Despite this, the dataset it replicated on the other end apparently perfectly without loss at the observation or variable level. sqlxrc is 0; sqlxmsg is null; sqlobs shows the correct number.

 

I'm mostly convinced that it is something to do with numeric, non-datetime variables, perhaps related to where those variables contain null values. But I can't pin it down to just one variable on one row.

 

I've seen references to this note a couple of times elsewhere, but the explanations for it don't appear to be relevant to what I'm doing.

 

Any ideas?

2 REPLIES 2
Tom
Super User Tom
Super User

Show the statement (and the full log for that statement).

 

Most likely you are trying to use an IMPLICIT passthrough to do the INSERT and SAS is saying it cannot execute the full statement in the remote database.  So something like this:

insert into mylib.mytable 
  select ....
;

If you think it should be able to do it completely in the remote database then use an EXPLICIT passthrough (just like you did to make the table).

execute by mylib
( inset into myschema.mytable 
  select ...
);
LinusH
Tourmaline | Level 20

Whenever when you encounter issues with implicit pass through, set these options:

options sastrace=',,,d' sastraceloc=saslog nostsuffix msglevel=i;

It might give you extra hints.

Data never sleeps

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
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
  • 2 replies
  • 1670 views
  • 0 likes
  • 3 in conversation