Using a macro variable for directory (PROC IMPORT)

[Negarev]

Hi All,

OK I'm really confused - I'm just trying to make my life easier by using a macro variable for the folder of my directory.

Here is the complete path of the file I'm trying to import:

\\Work\SAS\EPG1V2\data\class_birthdate.csv

If I run proc import, it works:

proc import datafile="\\Work\SAS\EPG1V2_EG\data\class_birthdate.csv"
dbms=csv out=work.class_birthdate_import
replace;
run;

Then I use my macro variable:

%let dataf = "\\Work\SAS\EPG1V2_EG\data";



proc import datafile=&dataf"\class_birthdate.csv"
dbms=csv out=work.class_birthdate_import
replace;

run;

But when I run that, it says it's an invalid physical name!! The weirdest thing is that this macro works when I use it for a LIBNAME statement for an Excel located in this exact same folder!! What am I doing wrong?

[Reeza]

Close...

%let dataf = \\Work\SAS\EPG1V2_EG\data;



proc import datafile="&dataf.\class_birthdate.csv"
dbms=csv out=work.class_birthdate_import
replace;

run;

---

@Negarev wrote:

Hi All,

OK I'm really confused - I'm just trying to make my life easier by using a macro variable for the folder of my directory.

Here is the complete path of the file I'm trying to import:

\\Work\SAS\EPG1V2\data\class_birthdate.csv

If I run proc import, it works:

proc import datafile="\\Work\SAS\EPG1V2_EG\data\class_birthdate.csv"
dbms=csv out=work.class_birthdate_import
replace;
run;

Then I use my macro variable:

%let dataf = "\\Work\SAS\EPG1V2_EG\data";



proc import datafile=&dataf"\class_birthdate.csv"
dbms=csv out=work.class_birthdate_import
replace;

run;

But when I run that, it says it's an invalid physical name!! The weirdest thing is that this macro works when I use it for a LIBNAME statement for an Excel located in this exact same folder!! What am I doing wrong?

---

[Negarev]

Nope. Doesn't work - here is the error message:
Syntax error, expecting one of the following: ;, DATAFILE, DATATABLE, DBMS, DEBUG, FILE, OUT, REPLACE, TABLE,
_DEBUG_.

[Tom]

So you told the macro processor to generate this SAS code:

proc import datafile="\\Work\SAS\EPG1V2_EG\data""\class_birthdate.csv"

Can you see the problem now?

[Negarev]

Thanks. I can see the problem. Not sure I can see the solution though...

I've tried:
CATX(&dataf,"\class_birthdate.csv")

No success though

[Tom]

The macro processor is just a simple text replacement tool.  So do not include text like quote characters that you do not need.  Do not include text that looks like function calls since those are just more text characters to the macro processor.  I seriously doubt that the name of the file you are trying to find starts with the uppercase letters CATX.

@Reeza has give the right answer.  Do not insert the quotes into the macro variable.  Expand the macro variable inside of the quotes so its value becomes part of the path.  Also make sure to use double quotes to enclose the string literal because the macro processor ignores macro triggers inside of strings bounded by single quotes instead of double quotes.

You might have left some unbalanced quotes from your earlier attempts that inserted a lot of extra quotes, so start with a new SAS session.

%let dataf = \\Work\SAS\EPG1V2_EG\data ;
proc import datafile="&dataf\class_birthdate.csv"
  dbms=csv out=work.class_birthdate_import
  replace
;
run;

[Negarev]

Thanks Tom. I've copied your code and pasted into my program, but no luck. I think I'm going to stay away from macro variables for now. This is so frustrating.

[Tom]

Copy the lines from the SAS log for the PROC IMPORT step and share.  Use the Insert Code button (looks like < / > ) to get a pop-up window to paste them so that the lines are not re-formatted by the forum editor.

[Negarev]

OK, so here is the log for the solution you provided:

proc import datafile="&dataf\class_birthdate.csv"
SYMBOLGEN: Macro variable DATAF resolves to
"\\Work\SAS\EPG1V2_EG\data"
PROCEDURE| _DISARM| STOP| _DISARM| 2021-03-25T04:08:07,323+00:00| _DISARM| WorkspaceServer| _DISARM| SAS| _DISARM| |
_DISARM| 64053248| _DISARM| 32612352| _DISARM| 11| _DISARM| 19| _DISARM| 227| _DISARM| 286792913| _DISARM| 0.000000| _DISARM|
0.001000| _DISARM| 1932264487.323000| _DISARM| 1932264487.324000| _DISARM| 0.000000| _DISARM| | _ENDDISARM
NOTE: PROCEDURE IMPORT used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

NOTE: The SAS System stopped processing this step because of errors.
NOTE: Line generated by the macro variable "DATAF".
32 ""\\Work\SAS\EPG1V2_EG\data"
_
22
76
ERROR 22-322: Syntax error, expecting one of the following: ;, DATAFILE, DATATABLE, DBMS, DEBUG, FILE, OUT, REPLACE, TABLE,
_DEBUG_.

ERROR 76-322: Syntax error, statement will be ignored.

Here the error is obvious : we're getting a quadruple quote. So V2 would be:

proc import datafile=&dataf"\class_birthdate.csv"
dbms=csv out=work.class_birthdate_import
replace
;
run;

Here is the log for that V2:

32 proc import datafile=&dataf"\class_birthdate.csv"
SYMBOLGEN: Macro variable DATAF resolves to
"\\Work\SAS\EPG1V2_EG\data"
33 dbms=csv out=work.class_birthdate_import
34 replace
35 ;
36 run;

NOTE: Unable to open parameter catalog: SASUSER.PARMS.PARMS.SLIST in update mode. Temporary parameter values will be saved to
WORK.PARMS.PARMS.SLIST.
ERROR: Invalid physical name.

For that V2, it is unable to find the file. So as was suggested before, I'll just add the dot (.) to join the two strings:

proc import datafile=&dataf."\class_birthdate.csv"
dbms=csv out=work.class_birthdate_import
replace
;
run;

Here is the log for that V3:

32 proc import datafile=&dataf."\class_birthdate.csv"
SYMBOLGEN: Macro variable DATAF resolves to
"\\Work\SAS\EPG1V2_EG\data"
33 dbms=csv out=work.class_birthdate_import
34 replace
35 ;
36 run;

NOTE: Unable to open parameter catalog: SASUSER.PARMS.PARMS.SLIST in update mode. Temporary parameter values will be saved to
WORK.PARMS.PARMS.SLIST.
ERROR: Invalid physical name.

Same prob...

[ballardw · Accepted solution]

---

@Negarev wrote:

OK, so here is the log for the solution you provided:

proc import datafile="&dataf\class_birthdate.csv"
SYMBOLGEN: Macro variable DATAF resolves to
"\\Work\SAS\EPG1V2_EG\data"
PROCEDURE| _DISARM| STOP| _DISARM| 2021-03-25T04:08:07,323+00:00| _DISARM| WorkspaceServer| _DISARM| SAS| _DISARM| |
_DISARM| 64053248| _DISARM| 32612352| _DISARM| 11| _DISARM| 19| _DISARM| 227| _DISARM| 286792913| _DISARM| 0.000000| _DISARM|
0.001000| _DISARM| 1932264487.323000| _DISARM| 1932264487.324000| _DISARM| 0.000000| _DISARM| | _ENDDISARM
NOTE: PROCEDURE IMPORT used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

NOTE: The SAS System stopped processing this step because of errors.
NOTE: Line generated by the macro variable "DATAF".
32 ""\\Work\SAS\EPG1V2_EG\data"
_
22
76
ERROR 22-322: Syntax error, expecting one of the following: ;, DATAFILE, DATATABLE, DBMS, DEBUG, FILE, OUT, REPLACE, TABLE,
_DEBUG_.

ERROR 76-322: Syntax error, statement will be ignored.

Here the error is obvious : we're getting a quadruple quote. So V2 would be:

proc import datafile=&dataf"\class_birthdate.csv"
dbms=csv out=work.class_birthdate_import
replace
;
run;

Here is the log for that V2:

32 proc import datafile=&dataf"\class_birthdate.csv"
SYMBOLGEN: Macro variable DATAF resolves to
"\\Work\SAS\EPG1V2_EG\data"
33 dbms=csv out=work.class_birthdate_import
34 replace
35 ;
36 run;

NOTE: Unable to open parameter catalog: SASUSER.PARMS.PARMS.SLIST in update mode. Temporary parameter values will be saved to
WORK.PARMS.PARMS.SLIST.
ERROR: Invalid physical name.

For that V2, it is unable to find the file. So as was suggested before, I'll just add the dot (.) to join the two strings:

proc import datafile=&dataf."\class_birthdate.csv"
dbms=csv out=work.class_birthdate_import
replace
;
run;

Here is the log for that V3:

32 proc import datafile=&dataf."\class_birthdate.csv"
SYMBOLGEN: Macro variable DATAF resolves to
"\\Work\SAS\EPG1V2_EG\data"
33 dbms=csv out=work.class_birthdate_import
34 replace
35 ;
36 run;

NOTE: Unable to open parameter catalog: SASUSER.PARMS.PARMS.SLIST in update mode. Temporary parameter values will be saved to
WORK.PARMS.PARMS.SLIST.
ERROR: Invalid physical name.

Same prob...

---

Large economy size hint: Do not include quotes as part of the macro variable.

Lets pick a small example code you can run. Look in the log to see the results.

%let vwithq="ABC";
%let vnoq  =ABC;

/* examine how these resolve with another value*/

%put Value inside quotes: "&vwithq.\file.csv";
%put Value inside quotes: "&vnoq.\file.csv";

Which will get

for the first: 
     
Value inside quotes: ""ABC"\file.csv"

And for the second:
Value inside quotes: "ABC\file.csv"

The first output above is what you are creating. Your LOG shows you that. The "quadruple quote" comes from the bit that you placed the quotes in definition of the macro variable. And then enclosed those quotes inside more quotes in the path part of the macro.

I will not say to never place quotes in a macro variable. But quite frequently the use means they will either not work generating errors as in this case, or generate unmatched quote pairs  the two quotes at the start are a matched pair meaning that the text of the value is no longer considered in quotes by the compiler. Results can be unpredictable.

Please go back and look carefully at @Reeza's first response. Note that there are NO QUOTES in the %let of the example solution.

And you have not removed them yet.

[Negarev]

Wow, thank you so much, it works now!

[Tom]

You did not include part of the log where the mistake was made.  But it is clear from the log that you have still added quotes to the value of the macro variable.

The SAS language compiler (and most languages) use quotes to distinguish string literals from object names.  So if you are writing SAS code like this:

put hello;
put "hello";

The first line is referencing a variable named hello and the second line is referencing a string literal with the value of hello.

The macro processor is not a computer language. It is just a text expansion tool.  It does not look for variables and strings and statements and procedures.  It just looks for triggers that say HEY process this part of the text.  The triggers are the & and % characters.  So if you want to set a macro variable to the value hello you would type:

%let x=hello;

and you have created a macro variable named X that contains five characters.  If instead you type:

%let x="hello";

instead of having a string with five characters you now have a string with seven characters.