CLI describe error

[sarahzhou]

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!

[AMSAS]

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.

1. Have you been able to access this table in the past?
2. When did you last access it successfully?
3. 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

[ballardw]

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."

[SASKiwi]

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.

[Reeza]

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.

[sarahzhou]

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!