topic using text macros in filepath in SAS Programming

using 2 text macros in data step (one with underscores)

ubshams — Mon, 28 Oct 2019 15:18:47 GMT

I am having issues using text macros in the data steps.

It may be because the filenames/datasets have underscores (but not sure). Unfortunately filenames will have to have underscores in our system's setup.

Trial code 1:

%let text1 = xyz_abc_def;

%let text2 = abc;

-----this works just fine (when I am typing full name of text1 and using one macro for text2)

data xyz_abc_def_&text2;

set oldfile;

...

run

--but when I try to use two macros in the data step i get errors:

data &text1_&text2;

set oldfile;

...

run

Error:

ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, /, ;, _DATA_, _LAST_, _NULL_.

So then I try this:

data "&text1"_&text2;

set oldfile;

...

run

Error:

ERROR: User does not have appropriate authorization level for library WC000001.

Please advice. Thanks in advance.

Re: using 2 text macros in data step (one with underscores)

PaigeMiller — Mon, 28 Oct 2019 15:23:04 GMT

First, let's get the terminology right. These are macro variables, not macros. Do not confuse the two.

To fix the problem, you need a dot after the name of the first macro variable to indicate that what follows the dot is considered text.

&text1._&text2

Otherwise SAS looks for a macro variable named &text1_ and there is no such macro variable defined.

using text macros in filepath

ubshams — Mon, 28 Oct 2019 22:57:22 GMT

On a somewhat related matter, I keep getting an error when trying to use a text macro in a proc import statement.

%let portfolio =portfolioname;

libname out "/folder1/folder2/folder3/folder4/folder5/folder6/&portfolio";

proc import out = out.samplefile_import datafile = "/folder1/folder2/folder3/folder4/folder5/folder6/&portfolio/filename_&portfolio._v2.xlsx"

Dbms=xlsx replace;

Sheet=EOP_Bal_Flow_Actuals;

Get following error:

WARNING: Missing semicolon between %THEN clause and ELSE has been assumed.

ERROR: Physical file does not exist, /folder1/folder2/folder3/folder4/folder5/folder6/portfolioname//Filename_portfolioname_v2.xlsx.

This occurs even though there is clearly an excel file called filename_portfolioname_v2.xlsx in the relevant folder. Also noticing two forward slashes in the error, not sure why.

Please advice. Thanks in Advance!

Re: using text macros in filepath

Tom — Mon, 28 Oct 2019 23:10:34 GMT

@ubshams wrote:

On a somewhat related matter, I keep getting an error when trying to use a text macro in a proc import statement.

%let portfolio =portfolioname;

libname out "/folder1/folder2/folder3/folder4/folder5/folder6/&portfolio";

proc import out = out.samplefile_import datafile = "/folder1/folder2/folder3/folder4/folder5/folder6/&portfolio/filename_&portfolio._v2.xlsx"

Dbms=xlsx replace;

Sheet=EOP_Bal_Flow_Actuals;

Get following error:

WARNING: Missing semicolon between %THEN clause and ELSE has been assumed.

ERROR: Physical file does not exist, /folder1/folder2/folder3/folder4/folder5/folder6/portfolioname//Filename_portfolioname_v2.xlsx.

This occurs even though there is clearly an excel file called filename_portfolioname_v2.xlsx in the relevant folder. Also noticing two forward slashes in the error, not sure why.

Please advice. Thanks in Advance!

You have clearly posted the wrong part of the program. There is neither an %IF nor a %THEN keyword in any of the code you posted.

Why does the message seem to be a mix between a macro error and a data step error. Why does it %THEN and ELSE instead of %THEN and %ELSE or THEN and ELSE?

Re: using text macros in filepath

ubshams — Mon, 28 Oct 2019 23:21:30 GMT

It might have been some global macros at the top of my code (some legacy companywide macros). Anyways I deleted those and just ran this part form the top and still get physicial file does not exist error.

%let portfolio =portfolioname;

libname out "/folder1/folder2/folder3/folder4/folder5/folder6/&portfolio";

proc import out = out.samplefile_import datafile = "/folder1/folder2/folder3/folder4/folder5/folder6/&portfolio/filename_&portfolio._v2.xlsx"

Dbms=xlsx replace;

Sheet=EOP_Bal_Flow_Actuals;

ERROR: Physical file does not exist, /folder1/folder2/folder3/folder4/folder5/folder6/portfolioname//Filename_portfolioname_v2.xlsx.

Re: using text macros in filepath

ScottBass — Tue, 29 Oct 2019 06:14:53 GMT

@ubshams wrote:

It might have been some global macros at the top of my code (some legacy companywide macros). Anyways I deleted those and just ran this part form the top and still get physicial file does not exist error.

%let portfolio =portfolioname;

libname out "/folder1/folder2/folder3/folder4/folder5/folder6/&portfolio";

proc import out = out.samplefile_import datafile = "/folder1/folder2/folder3/folder4/folder5/folder6/&portfolio/filename_&portfolio._v2.xlsx"

Dbms=xlsx replace;

Sheet=EOP_Bal_Flow_Actuals;

ERROR: Physical file does not exist, /folder1/folder2/folder3/folder4/folder5/folder6/portfolioname//Filename_portfolioname_v2.xlsx.

1) run your code with options symbolgen mprint mlogic (mlogic probably not needed but doesn't hurt), then cut-and-paste the relevant portion of your log (from the source code echoed in the log to the error message). Paste it using the Insert SAS Code... icon.

2) For now forget macro. Get this exact code to work properly (edit as required):

proc import 
   out = out.samplefile_import 
   datafile = "/folder1/folder2/folder3/folder4/folder5/folder6/portfolioname/filename_portfolioname_v2.xlsx"
   dbms=xlsx 
   replace;
   Sheet=EOP_Bal_Flow_Actuals;
run;

3) Once you get the exact code to work properly, since your only macro variables are in the datafile path, just do this:

%put /folder1/folder2/folder3/folder4/folder5/folder6/&portfolio/filename_&portfolio._v2.xlsx;

and just review the log until the %put statement exactly equals the code (filepath) that you proved worked as desired.

Re: using text macros in filepath

ScottBass — Tue, 29 Oct 2019 06:17:18 GMT

P.S.: Don't mark a post resolved, then post "On a somewhat related matter...". Either unmark the resolved post or post a new thread. Many members will just ignore posts that are resolved, so you're limiting your pool of potential solutions (and possibly lengthening the time to get a solution).

Re: using text macros in filepath

ubshams — Tue, 29 Oct 2019 19:43:44 GMT

The portfolioname macro in the context of filename is case sensitive it turns out. That fixed it. Thanks for all your suggestions!

Re: using text macros in filepath

ScottBass — Wed, 30 Oct 2019 05:29:54 GMT

@ubshams wrote:

The portfolioname macro in the context of filename is case sensitive it turns out. That fixed it. Thanks for all your suggestions!

Yeah unix/linux is like that :rolleyes: