topic How to Prevent Resolution of Ampersand? in SAS Programming

How to Prevent Resolution of Ampersand?

Junyong — Tue, 28 Mar 2023 21:37:58 GMT

I have an Excel file S&P 500_230328.xlsx on my desktop and want to rename it as S&P 500_230329.xlsx via infile pipe as follows:

data _null_; infile "ren %sysget(USERPROFILE)\Desktop\""S%nrstr(&)P 500_%sysfunc(putn(%sysfunc(today()),yymmddn6.))"".xlsx ""S%nrstr(&)P 500_%sysfunc(putn(%sysfunc(today())+1,yymmddn6.))"".xlsx" pipe; run;

The problem arises because SAS considers &P as a macro expression. I tried both %str() and %nrstr() for the ampersands above but failed (it seems %superq() doesn't work as well); I cannot use single quotes because of the date changes. How can I prevent WARNING: Apparent symbolic reference P not resolved. in this case?

Re: How to Prevent Resolution of Ampersand?

Tom — Tue, 28 Mar 2023 21:47:34 GMT

Use the FILEVAR= option on the INFILE statement so that you can use a data set variable. Then you don't need to bother the macro processor at all.

data _null_;
  length file1 file2 cmd $500 ;
  file1=quote(cats(sysget('USERPROFILE'),'\Desktop\S&P 500_',put(today(),yymmddn6.),'.xlsx'));
  file2=quote(cats('S&P 500_',put(today()+1,yymmddn6.),'.xlsx'));
  cmd=catx(' ','ren',file1,file2);
  infile cmd pipe filevar=cmd ;
  input;
  put _infile_;
run;

PS Why are you generating date strings without the century? https://en.wikipedia.org/wiki/Year_2000_problem

Re: How to Prevent Resolution of Ampersand?

Junyong — Tue, 28 Mar 2023 23:35:08 GMT

Thanks—I tend to code Command Prompt and pipe directly for readability. It seems that, according to your filevar suggestion, this ampersand resolution issue, unlike others, cannot be addressed by %str, %quote, or %superq families.

Re: How to Prevent Resolution of Ampersand?

Tom — Wed, 29 Mar 2023 06:14:59 GMT

Not sure how complex macro code makes things readable, but you can certainly use %NRSTR() to protect & characters. You just need to remember not to unquote them before you can protect them with actual quotes.

You can use the actual QUOTE() function to add single quotes around the string.

63   %let file1="%sysget(USERPROFILE)\Desktop\%nrstr(S&P) 500_%sysfunc(today(),yymmddn6).xlsx";
64   %let file2="%nrstr(S&P) 500_%sysfunc(putn(%sysfunc(today())+1,yymmddn6.)).xlsx";
65
66   data _null_;
67     infile %sysfunc(quote(ren &file1 &file2,%str(%'))) pipe;
68     input;
69     put _infile_;
70   run;

NOTE: The infile 'ren "C:\Users\xxx\Desktop\S&P 500_230329.xlsx" "S&P 500_230330.xlsx"' is:
      Unnamed Pipe Access Device,
      PROCESS=ren "C:\Users\xxx\Desktop\S&P 500_230329.xlsx" "S&P 500_230330.xlsx",
      RECFM=V,LRECL=32767

Stderr output:
The system cannot find the file specified.
NOTE: 0 records were read from the infile 'ren "C:\Users\xxx\Desktop\S&P 500_230329.xlsx" "S&P 500_230330.xlsx"'.
NOTE: DATA statement used (Total process time):
      real time           0.07 seconds
      cpu time            0.00 seconds

Or if you know that the string does not contain any single quotes you can use %BQUOTE() to get the single quotes, but you will need to use %unquote() to remove the macro quoting so that SAS can see that you actually passed a quoted string to the INFILE statement.

data _null_;
  infile %unquote(%bquote('ren &file1 &file2')) pipe;
  input;
  put _infile_;
run;