topic Re: Macro variable for PATH created using SQL not working in libname statement. in SAS Programming

Macro variable for PATH created using SQL not working in libname statement.

SASuserlot — Wed, 16 Nov 2022 01:06:24 GMT

Probably this can be a blind question, Since I can not share the path I am working. Sorry for that.

I created a macro variable (Lib) using the dataset that contains the list of paths under the variable name 'Path' . When I try to create the library using libname statement I am getting the following error image with the quoted string NOTE ( my path did not have any quotes ).

My macro variable is resolving to the string that I expected. But library is not created. If I copy the value from the path variable and create a different dataset and creates the macro variable then it working. I am not sure what I am doing wrong. Can you please share your ideas what I need to check to correct it or what I am doing wrong (like you think any visible characters or spaces any thing to check).

*libname not working;
options mprint mlogic symbolgen;
proc sql noprint;
select  strip(path)  into: lib
			 from county;
quit;

libname x "&lib" ;
***************************************;

*libname statement working this method;
data file;
path= 'c:/actual/path/name';
run;

proc sql noprint;

select  strip(path)  into: lib
			 from file;
quit;

libname x "&lib" ;

/*The actual path something looks similar below
path= G:/section/accounts/expenses-22/20 22/NJ/burlington/county/continuity/maintainanc;*/

Re: Macro variable for PATH created using SQL not working in libname statement.

Quentin — Wed, 16 Nov 2022 02:17:14 GMT

With this sort of problem, often the cause is an unmatched quote earlier in your SAS session.

The easiest way to test would be to make a permanent dataset temp.county. Then start a new session and submit just:

proc sql noprint;
select  strip(path)  into: lib
			 from temp.county;
quit;

%put >>&=lib<< ;

libname x "&lib" ;

If that code works, it means your problem is somewhere earlier in your SAS session.

Re: Macro variable for PATH created using SQL not working in libname statement.

SASuserlot — Wed, 16 Nov 2022 02:46:13 GMT

Thank you @Quentin . Yes, there is a note like that where I created a macro variable of the concatenation of 50 state names, separated by 'comma' ( this variable is longer than 262 characters in length)to run a do loop ( it didn't affect anywhere else in my code except troubling the libname statement). How Can I counteract this issue? Because I need this in the same session. Using 'NOQUOTELEMAX' will do anything?

Re: Macro variable for PATH created using SQL not working in libname statement.

Tom — Wed, 16 Nov 2022 03:52:00 GMT

Using the STRIP() function is NOT going to prevent PROC SQL from adding the trailing spaces implied by the LENGTH of the variable selected into the resulting macro variable.

Instead use the TRIMMED keyword.

proc sql noprint;
select path
  into :lib trimmed
  from county
;
quit;

If you are going to add back the quotes later then include them in the macro variable. This is especially important if the value already has the quote character. Use the optional second parameter of the quote() function to have it use single quotes. That is important if the string has macro trigger characters like & or % that would be evaluated by the macro processor if the string was bounded by double quote characters.

proc sql noprint;
select quote(trim(path),"'")
  into :lib trimmed
  from county
;
quit;
libname x &lib ;

Or skip PROC SQL and MACRO code completely.

data _null_;
  set county;
  rc=libname('x',path);
  put rc= path=;
  stop;
run;

Re: Macro variable for PATH created using SQL not working in libname statement.

SASuserlot — Wed, 16 Nov 2022 14:14:59 GMT

Thank you @Tom Your second method using the quote and trim functions worked for me. Thank you again sharing your knowledge to me. I really don't like these 'Quoting' Issues in Macros.😣

Re: Macro variable for PATH created using SQL not working in libname statement.

ballardw — Wed, 16 Nov 2022 18:20:19 GMT

@SASuserlot wrote:

Thank you @Quentin . Yes, there is a note like that where I created a macro variable of the concatenation of 50 state names, separated by 'comma' ( this variable is longer than 262 characters in length)to run a do loop ( it didn't affect anywhere else in my code except troubling the libname statement). How Can I counteract this issue? Because I need this in the same session. Using 'NOQUOTELEMAX' will do anything?

An aside to your original issue: Placing 50 state names into a single macro variable for a loop control seems like it is quite possible to approach the problem differently. Not so much a size of variable issue as whether the use of a macro variable is actually needed at all.

Re: Macro variable for PATH created using SQL not working in libname statement.

SASuserlot — Wed, 16 Nov 2022 18:43:38 GMT

Thank you @ballardw for bringing this attention. I am kind of new at macros, so I am starting at small macros and on my way to go complex. Yes I do understand what you saying. Macro variable was created to run through a do - loop macro where every time , it has to choose the state based on the iteration ( created separate number for the iteration) in order to avoid every time entering the state name when calling the macro. I wish I could I can show my code, so that I can get better and easy option from you guys ( unfortunately my code have lot of privacy information, Usually I try to give dummy code as much as I can share or piece of code where I stuck).