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!
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.
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
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."
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.
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!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.