topic Re: CLI describe error in SAS Programming

CLI describe error

sarahzhou — Wed, 12 Jan 2022 15:16:34 GMT

Hi, I would like to select two member information, normal member can be identity by member ID, whereas premium member can be identified by PREM_MEMBER_ID. Each member has an unique member_key.

Each member_id can be identified by member_id, user_name or google_account.

The script as below:

connect to TARGETSVR as DESKTOP(datasrc="COMPANY" authdomain="");

select compress("'"||%nrquote(MEMBER_KEY)||"'") into: MEMBER_KEY separated by ',' 
from connection to DESKTOP
    (select distinct * from SHOPPING_2021
	where (MEMBER_ID = %str(&MEMBER_ID.) OR USER_NAME = %str(&MEMBER_ID.) OR 
    (GOOGLE_ACCOUNT = %str(&MEMBER_ID.) &DOB.))
	);

select compress("'"||%nrquote(MEMBER_KEY)||"'") into: PREM_MEMBER_KEY separated by ',' 
from connection to SQLTGT
	(select distinct * from SHOPPING_2021 
     where MEMBER_ID = %str(&PREM_MEMBER_ID.) OR USER_NAME = %str(&PREM_MEMBER_ID.) OR 
     (GOOGLE_ACCOUNT = %str(&PREM_MEMBER_ID.) &PREM_DOB_Filt.)
	 );

However, I get error:

ERROR: CLI describe error: [SAS][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]Incorrect syntax near ')'. : [SAS][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]The batch could not be analyzed because of compile errors.

How can I fix this?

Please advise, thanks!

Re: CLI describe error

AMSAS — Wed, 12 Jan 2022 17:03:13 GMT

Run a simple query e.g. select count(*) from shopping_2021 does it work?

If you get the same error, then you have some kind of connection issue.

Have you been able to access this table in the past?
When did you last access it successfully?
What changed?

If the simple query runs, then start building your complex query up, step by step adn testing it each step of the way.

Until you determine what is breaking it

Re: CLI describe error

ballardw — Wed, 12 Jan 2022 17:19:07 GMT

What is the purpose of using %nrquote(Member_key)? From the code usage Member_key should be a variable in a data set and should not require macro quoting.

I strongly suggest showing us the LOG for the entire Proc SQL (or which ever procedure you are using) to include all of the notes, messages and errors. Copy the text from the log, open a text box on the forum with the </> icon that appears above the message window and then paste the text. XXXX out any sensitive connection elements like passwords if present.

I have a hunch that perhaps other issues may be involved.

Perhaps you are overusing macro quoting functions and maybe should be using something more like:

where MEMBER_ID = "&MEMBER_ID."

Re: CLI describe error

SASKiwi — Wed, 12 Jan 2022 21:15:45 GMT

It's near impossible to help you because a lot of your SQL logic is buried in macro variables and you haven't provided the values for these.

Please post your complete SAS log after adding this - options mprint symbolgen; - to the start of your program. This should print what is in your macro variables in the log.

Re: CLI describe error

Reeza — Thu, 13 Jan 2022 02:15:51 GMT

You're using explicit SQL Pass thru so your SQL needs to be SQL Server compliant, not SAS SQL and your code looks like it's all SQL.

You're asking SQL server to create macro variables but SQL server doesn't really have the same concept.
select compress("'"||%nrquote(MEMBER_KEY)||"'") into: MEMBER_KEY separated by ',' from connection to DESKTOP

Unless I'm missing something you're using the entirely wrong approach to whatever you're trying to do here.

Re: CLI describe error

sarahzhou — Thu, 13 Jan 2022 16:22:23 GMT

Hi, thank you for your reply. Your advice really give me the insight to solve my issues. I added another macro variable in my script, and it run success. Thanks!