PROC SQL, SQL PASSTHROUGH ERROR IN CODE

Reply
Contributor
Posts: 27

PROC SQL, SQL PASSTHROUGH ERROR IN CODE

THIS CODE DIDNT WORK.

proc sql;
connect to odbc as xyz (dsn=xyz uid="&lan_usr" pwd="&lan_pwd");

create table ABCD as
select * from connection to xyz(
   select distinct A   from ABC   where ***
       ))
;

disconnect from xyz;
quit;

Versus

Worked code .. Removing double quotes in sql passthrough- is working ....

proc sql;
connect to odbc as xyz (dsn=xyz uid=&lan_usr pwd=&lan_pwd);

create table ABCD as
select * from connection to xyz(
   select distinct A   from ABC   where ***
       ))
;

disconnect from xyz;
quit;

Trying to understand why quotes made difference... 

Respected Advisor
Posts: 4,657

Re: PROC SQL, SQL PASSTHROUGH ERROR IN CODE

Try using OPTION MPRINT; to see what the submitted code was after macro substitution.

PG

PG
Contributor
Posts: 27

Re: PROC SQL, SQL PASSTHROUGH ERROR IN CODE

PGStats, thanks for your reply.

MPRINT shows- CLI Error- error trying to establish  connection...

Same error is shown in log without using that option..

Instead, when I hard code the values and put the dataset name and password in quotes... - still same error

I am guessing nothing to do with the MACRO

Contributor
Posts: 27

Re: PROC SQL, SQL PASSTHROUGH ERROR IN CODE

PGSTATS, thank you for your reply.

used mprint- it says "

 

ERROR: CLI describe error: [OpenLink][ODBC][SQL Server]Statement(s) could not be prepared (0) : [OpenLink][ODBC][SQL Server]General

SQL Server error: Check messages from the SQL Server (102)

"

I tried removing macro and hard quoting dsn and pw. still same error.

i am basically trying to understand why double quotes cannot be used..


Super User
Posts: 17,907

Re: PROC SQL, SQL PASSTHROUGH ERROR IN CODE

Is this code run in open code or through a macro?

Contributor
Posts: 27

Re: PROC SQL, SQL PASSTHROUGH ERROR IN CODE

i tried both. thanks

Valued Guide
Posts: 3,208

Re: PROC SQL, SQL PASSTHROUGH ERROR IN CODE

Within sas language the difference in single quote and double quote is documented and we'll known to sas coders. When using explicit paas through you have to follow the sql language of the external used dbms not the sas language.

Sometimes only single quotes are allowed. Your situation looks to be this situation.

You still can use sas macro language as that is done with all source lines passed  by SAS.

As your problem looks to be some variable coding to be filled in user/pswd. My macro xkeypsw could help you.

---->-- ja karman --<-----
Contributor
Posts: 27

Re: PROC SQL, SQL PASSTHROUGH ERROR IN CODE

thanks for your reply. quotes did not work for me at all.. not even single quotes.. no quotes worked perfect! thanks

Ask a Question
Discussion stats
  • 7 replies
  • 1083 views
  • 0 likes
  • 4 in conversation