I am trying to load a dataset from the microsoft SQL server into SAS using Proc SQL (as shown below)
One of the variable name contains the "&" character, which SAS interpreted as a macro variable reference, giving out a warning message 'WARNING: Apparent symbolic reference B not resolved.'
I am wondering what would be the proper way to reference this variable. Apparently quoting the variable name as literal 'A&B Variable'n does not work in the Proc SQL setting as I've tried it.
proc sql; connect to sqlsvr(dsn=XXXX user=AAAA password=****); create table New as select * from connection to sqlsvr ( Select Base.[ID] as ID, Base.[A&B Variable] as AB_Var from InputDateSet as Base ); quit;
Thanks in advance for your help.
Did you try %NRQUOTE. It works for macros. no harm trying it.
Tried that too: Base.[%nrquote(A&B Variable)]. Same warning msg pops up.
How about %NRSTR:
%let test = %NRSTR(A&B);
%put test = &test;
%NRSTR worked! Thanks very much.
Base.[%NRSTR(A&B Variable)]
try OPTIONS VALIDVARNAME=V7; and then import
2 part solution
part 1: rename all similarly named columns in the database with names that will work over all environments (only use characters a-z, 0-9, underline)
part 2: shoot the one who had the spectacularly foolish idea to name a database column "A&B Variable"
just kidding about part 2, but part 1 will save you lots of grief in the future.
Unfortunately this is a read-only database to me and I have no control over how the variables are named.
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!
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.