topic Re: Literal variable name treatment in Proc SQL in SAS Data Management

Literal variable name treatment in Proc SQL

JerryW — Wed, 09 Sep 2015 21:20:28 GMT

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.

Re: Literal variable name treatment in Proc SQL

stevyfargose — Wed, 09 Sep 2015 21:29:20 GMT

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

Re: Literal variable name treatment in Proc SQL

JerryW — Wed, 09 Sep 2015 21:34:41 GMT

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

Re: Literal variable name treatment in Proc SQL

SASKiwi — Wed, 09 Sep 2015 22:19:10 GMT

How about %NRSTR:

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

Re: Literal variable name treatment in Proc SQL

stevyfargose — Wed, 09 Sep 2015 22:20:21 GMT

try OPTIONS VALIDVARNAME=V7; and then import

Re: Literal variable name treatment in Proc SQL

Kurt_Bremser — Thu, 10 Sep 2015 05:35:03 GMT

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.

Re: Literal variable name treatment in Proc SQL

JerryW — Thu, 10 Sep 2015 14:23:25 GMT

%NRSTR worked! Thanks very much.

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

Re: Literal variable name treatment in Proc SQL

JerryW — Thu, 10 Sep 2015 14:25:02 GMT

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