BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
Junyong
Pyrite | Level 9

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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;

 

 

View solution in original post

3 REPLIES 3
Tom
Super User Tom
Super User

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

 

 

Junyong
Pyrite | Level 9

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.

Tom
Super User Tom
Super User

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;

 

 

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 3 replies
  • 858 views
  • 0 likes
  • 2 in conversation