SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Literal variable name treatment in Proc SQL

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 7
Accepted Solution

Literal variable name treatment in Proc SQL

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. 


Accepted Solutions
Solution
‎09-25-2015 06:23 AM
Super User
Posts: 3,112

Re: Literal variable name treatment in Proc SQL

How about %NRSTR:

 

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

View solution in original post


All Replies
Occasional Contributor
Posts: 12

Re: Literal variable name treatment in Proc SQL

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

Occasional Contributor
Posts: 7

Re: Literal variable name treatment in Proc SQL

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

 

Solution
‎09-25-2015 06:23 AM
Super User
Posts: 3,112

Re: Literal variable name treatment in Proc SQL

How about %NRSTR:

 

%let test = %NRSTR(A&B);
%put test = &test;
Occasional Contributor
Posts: 7

Re: Literal variable name treatment in Proc SQL

[ Edited ]

%NRSTR worked! Thanks very much. 

 

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

 

Occasional Contributor
Posts: 12

Re: Literal variable name treatment in Proc SQL

try OPTIONS VALIDVARNAME=V7; and then import

Super User
Posts: 6,963

Re: Literal variable name treatment in Proc SQL

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 7

Re: Literal variable name treatment in Proc SQL

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

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