BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
JerryW
Calcite | Level 5

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. 

1 ACCEPTED SOLUTION

Accepted Solutions
SASKiwi
PROC Star

How about %NRSTR:

 

%let test = %NRSTR(A&B);
%put test = &test;

View solution in original post

7 REPLIES 7
stevyfargose
Obsidian | Level 7

Did you try %NRQUOTE. It works for macros. no harm trying it.

JerryW
Calcite | Level 5

Tried that too: Base.[%nrquote(A&B Variable)]. Same warning msg pops up.  Man Sad

 

SASKiwi
PROC Star

How about %NRSTR:

 

%let test = %NRSTR(A&B);
%put test = &test;
JerryW
Calcite | Level 5

%NRSTR worked! Thanks very much. 

 

Base.[%NRSTR(A&B Variable)]

 

stevyfargose
Obsidian | Level 7

try OPTIONS VALIDVARNAME=V7; and then import

Kurt_Bremser
Super User

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.

JerryW
Calcite | Level 5

Unfortunately this is a read-only database to me and I have no control over how the variables are named. 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

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.

Discussion stats
  • 7 replies
  • 2741 views
  • 0 likes
  • 4 in conversation