Error reading files with apostrophes in the title

[stewart_Jardine]

Hi all,

this seems like it should be a really easy one to resolve but for the life of me I cant figure it out.

I have a massive macro loop that reads in a lot of files and it was constantly failing, in debugging the issue I have discovered that the issue was being caused by trying to read a file name with an apostrophe in the title

I am using SAS VIYA 4 (2023.10) in SAS Studio

in the below code you can see that I declare 2 macro variables (file_test and file_test2) both of these files exist but the code only works when running the filename statement using file_test2

%let file_test = "SICR Testing Sep'21 with new definition.sas";

%let file_test2 = "SICR Testing Live.sas";

filename inp filesrvc  filename=&file_test2 folderpath="/ALD/Model Development/Programs/Development/IFRS9/ANNUAL_REVIEWS_2024/RM/SICR";

data temp;
infile inp;
input location $400. filename $400. code $200.;
if index(lowcase(code),"import") ne 0 then do;
import=1;
output;
end;

if index(lowcase(code),"libname") ne 0 then do;
libname=1;
output;
end;

if index(lowcase(code),"test") ne 0 then do;
search_term=1;
output;
end;
run;

the error generated when using file_test instead of file_test2 is:

1     /* region: Generated preamble */
2     /* Make sure the current directory is writable */
3     data _null_;
4         length rc 4;
5         %let tworkloc="%sysfunc(getoption(work))";
6         rc=dlgcdir(&tworkloc);
7     run;
NOTE: The current working directory is now 
      "/data/saswork/SAS_workB69400000215_sas-compute-server-16a567e7-8c96-4bab-ae30-61dcf3bcc136-9370".
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      user cpu time       0.00 seconds
      system cpu time     0.00 seconds
      memory              405.50k
      OS Memory           26568.00k
      Timestamp           01/11/2024 11:55:10 AM
      Step Count                        88  Switch Count  0
      Page Faults                       0
      Page Reclaims                     12
      Page Swaps                        0
      Voluntary Context Switches        0
      Involuntary Context Switches      0
      Block Input Operations            0
      Block Output Operations           0
      
8     
9     /* Setup options */
10    title;
11    footnote;
12    options validvarname=any;
13    options validmemname=extend;
14    options dtreset date number;
15    options device=png;
16    
17    /* Setup macro variables */
18    %let syscc=0;
19    %let _clientapp = %nrquote(%nrstr(SAS Studio));
20    %let _clientappabbrev = %nrquote(%nrstr(Studio));
21    %let _clientappversion=2023.10;
22    %let _clientversion=;
23    %let _sasservername=&SYSHOSTNAME;
24    %let _sashostname=&SYSHOSTNAME;
25    %let _sasprogramfilehost=&SYSHOSTNAME;
26    %let _clientuserid = %nrquote(%nrstr(Stewart.Jardine@aldermore.co.uk));
27    %let _clientusername = %nrquote(%nrstr(Stewart Jardine));
28    %let clientmachine = %nrquote(%nrstr());
29    %let _clientmachine = %nrquote(%nrstr());
30    %let _clientmode = %nrquote(%nrstr(viya));
31    %let sasworklocation="%sysfunc(getoption(work))/";
32    filename _cwd &sasworklocation;
33    data _null_;
34        call symput('_sasworkingdir',pathname('_cwd'));
35    run;
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      user cpu time       0.00 seconds
      system cpu time     0.00 seconds
      memory              478.59k
      OS Memory           26568.00k
      Timestamp           01/11/2024 11:55:10 AM
      Step Count                        89  Switch Count  0
      Page Faults                       0
      Page Reclaims                     10
      Page Swaps                        0
      Voluntary Context Switches        0
      Involuntary Context Switches      0
      Block Input Operations            0
      Block Output Operations           0
      
36    filename _cwd;
NOTE: Fileref _CWD has been deassigned.
37    %let _sasprogramfile = %nrquote(%nrstr());
38    %let _baseurl = %nrquote(%nrstr(https://aldermore.ondemand.sas.com/SASStudio/));
39    %let _execenv = %nrquote(%nrstr(SASStudio));
40    %symdel _dataout_mime_type _dataout_name _dataout_url _dataout_table / nowarn;
41    %let _sasws_ = %bquote(%sysfunc(getoption(work)));
42    %let _saswstemp_ = %bquote(%sysfunc(getoption(work)));
43    
44    /* Detect SAS/Graph and setup graph options */
45    data _null_;
46        length rc $255;
47        call symput("graphinit","");
48        call symput("graphterm","");
49        rc=tslvl('sasxgopt','n');
50        _error_=0;
51        if (rc^=' ') then do;
52            call symput("graphinit","goptions reset=all gsfname=_gsfname;");
53            call symput("graphterm","goptions noaccessible;");
54        end;
55    run;
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      user cpu time       0.00 seconds
      system cpu time     0.00 seconds
      memory              479.65k
      OS Memory           26568.00k
      Timestamp           01/11/2024 11:55:10 AM
      Step Count                        90  Switch Count  0
      Page Faults                       0
      Page Reclaims                     19
      Page Swaps                        0
      Voluntary Context Switches        1
      Involuntary Context Switches      0
      Block Input Operations            0
      Block Output Operations           0
      
56    data _null_;
57        length rc 4;
58        rc=sysprod("PRODNUM002");
59        if (rc^=1) then do;
60            call symput("graphinit","");
61            call symput("graphterm","");
62        end;
63    run;
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      user cpu time       0.00 seconds
      system cpu time     0.00 seconds
      memory              492.43k
      OS Memory           26568.00k
      Timestamp           01/11/2024 11:55:10 AM
      Step Count                        91  Switch Count  0
      Page Faults                       0
      Page Reclaims                     10
      Page Swaps                        0
      Voluntary Context Switches        0
      Involuntary Context Switches      0
      Block Input Operations            0
      Block Output Operations           0
      
64    
65    /* Setup ODS destinations */
66    ods _all_ close;
67    %studio_results_directory;
68    filename _htmlout "&_results_prefix_..html";
69    filename _listout "&_results_prefix_..lst";
70    filename _gsfname temp;
71    filename _dataout "&_results_prefix_..dat";
72    ods autonavigate off;
73    ods graphics on;
74    ods html5 (id=web) METATEXT='http-equiv="Content-Security-Policy" content="default-src ''none''; style-src ''unsafe-inline'';
74  ! img-src data: ;"' device=png gpath="&_saswstemp_" path="&_saswstemp_" encoding=utf8 file=_htmlout (title='Results:SAS Program
74  ! 1.sas') style=Illuminate options(bitmap_mode='inline' outline='on' svg_mode='inline' css_prefix=".ods_&SYS_COMPUTE_JOB_ID"
74  ! body_id="div_&SYS_COMPUTE_JOB_ID" );
NOTE: Writing HTML5(WEB) Body file: _HTMLOUT
75    ods listing file=_listout;
76    &graphinit;
77    %studio_initialize_custom_output;
78    /* endregion */
79    
80    %let file_test = "SICR Testing Sep'21 with new definition.sas";
81    
82    %let file_test2 = "SICR Testing Live.sas";
83    
84    filename inp filesrvc  filename=&file_test folderpath="/ALD/Model
84  ! Development/Programs/Development/IFRS9/ANNUAL_REVIEWS_2024/RM/SICR";
85    
86    data temp;
87    infile inp;
88    input location $400. filename $400. code $200.;
89    if index(lowcase(code),"import") ne 0 then do;
90    import=1;
91    output;
92    end;
93    
94    if index(lowcase(code),"libname") ne 0 then do;
95    libname=1;
96    output;
97    end;
98    
99    if index(lowcase(code),"test") ne 0 then do;
100   search_term=1;
101   output;
102   end;
103   run;
ERROR: Physical file does not exist, SICR Testing Sep'21 with new definition.sas.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: Due to ERROR(s) above, SAS set option OBS=0, enabling syntax check mode. 
      This prevents execution of subsequent data modification statements.
WARNING: The data set WORK.TEMP may be incomplete.  When this step was stopped there were 0 observations and 6 variables.
WARNING: Data set WORK.TEMP was not replaced because this step was stopped.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      user cpu time       0.00 seconds
      system cpu time     0.00 seconds
      memory              532.03k
      OS Memory           30924.00k
      Timestamp           01/11/2024 11:55:10 AM
      Step Count                        92  Switch Count  0
      Page Faults                       0
      Page Reclaims                     35
      Page Swaps                        0
      Voluntary Context Switches        0
      Involuntary Context Switches      1
      Block Input Operations            0
      Block Output Operations           8
      
104   
105   /* region: Generated postamble */
106   /* Close ODS destinations */
107   &graphterm; ;*';*";*/;run;quit;
108   quit;run;
109   ods html5 (id=web) close;
110   ods listing close;
111   %if %sysfunc(fileref(_gsfname)) lt 0 %then %do;
112       filename _gsfname clear;
NOTE: Fileref _GSFNAME has been deassigned.
113   %end;
114   %studio_capture_custom_output;
115   /* endregion */
116   

can someone help me out

[Kurt_Bremser]

Get rid of the single quote in the first place. Such characters (which have special meaning in many environments) will often cause problems.

On top of it, you might have a UTF character in there which only looks like a standard single quote.

[stewart_Jardine]

unfortunately this file represents one of about 3.5k files generated by nearly 100 users so i cant go through and check everyone is not using single quotes and also stop them from using them in the future, there must be way to code in some redundancy so that this isn't an issue

[Kurt_Bremser]

After sufficient application of a LART, even the most stubborn users will come to their senses 😉

Make it clear that only these characters have a place in file or other object names intended for automated processing:

• the 26 characters of the western alphabet, can be upper or lower case
• the ten arabic digits 0 to 9
• underlines

Even blanks should be avoided (Maxim 44).

Adhering to these rules is not rocket science, and even persons of mediocre intelligence should manage that.

[yabwon]

Agree with @Kurt_Bremser 's opinion. Get rid that apostrophe in the file name ( not sure if you are aware but there is a dedicated place in hell for programmers who names file like that 😉 🙂 )

BTW. Did you try just

filename inp "/ALD/Model Development/Programs/Development/IFRS9/ANNUAL_REVIEWS_2024/RM/SICR/&file_test.";

to see what will happen?

Bart

[stewart_Jardine]

hey Bart

I completely agree that it would be best to correct the file name, however as I said to Kurt, this is just one sample of thousands of files and I don't want to have to constantly be checking up on hundreds of users or debugging my code if they make the same mistake. I would rather code in some redundancy to deal with it.

unfortunately your suggestion of not using filename and folder-path aren't an option when you use filesrvc to read SAS content

[yabwon]

one more option to try, make the macrovariable:

%let file_test = "'SICR Testing Sep''21 with new definition.sas'";

(additional apostrophes inside at the begginig and the end of string, and "doubled" the one next to 21)

Bart

[Tom]

The error message clearly says that the requested file does not exist.

Since it is not a real file, but something you had to use the FILESRV engine to access what makes you confident that you have the right name?  Note that it is possible on most operating systems to create files with strange names that include invisible character, or even just trailing spaces.

If it is the right name then open a ticket with SAS support to see if there is any work around.

Perhaps there some other function you can call to define the fileref.  Or perhaps rename the object to have a name that does not cause an error.

[stewart_Jardine]

Hi Tom,

thanks for the message, it is a real file, it is stored in SAS content folder and uses the SAS FILESRVC acces method from their documentation to access it:

https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/lestmtsglobal/p0qapul7pyz9hmn0zfoefj0c278a.ht...

I am 100 % confident that the file exists as I can see it in SAS content along with the other file 

[Kurt_Bremser]

To narrow the issue down, I would manually remove the quote in the filename in the GUI (position the cursor right of it, hit backspace and enter) and see if that fixes it.

Also, use this adapted example from the page you linked:

filename myfldr filesrvc folderpath="/ALD/Model Development/Programs/Development/IFRS9/ANNUAL_REVIEWS_2024/RM/SICR";

data files_in_service;
   did = dopen('myfldr');
   mcount = dnum(did);
   do i=1 to mcount;
      memname = dread(did, i);
      output;
   end;  
   rc = dclose(did);
   keep memname;
run;

to get a dataset with filenames. You can then use techniques like a display with $HEX to find out what you really have in there.

And if it turns out that the single quote is really the lone culprit, open a ticket with SAS technical support, and notify your users that, at least for the time being, single quotes are VERBOTEN.

[Tom]

---

@stewart_Jardine wrote:

Hi Tom,

thanks for the message, it is a real file, it is stored in SAS content folder and uses the SAS FILESRVC acces method from their documentation to access it:

https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/lestmtsglobal/p0qapul7pyz9hmn0zfoefj0c278a.ht...

I am 100 % confident that the file exists as I can see it in SAS content along with the other file 

---

I just meant it is not using a normal filesystem.  So you are using something SAS has invented for VIYA.  Since it does not have the 40+ years of debugging that using normal file system access does there might still be some kinks in their programming.  SAS Technical support is the best way forward.

[FreelanceReinh]

Hi @stewart_Jardine,

Maybe you can replace the offending character with a wildcard character of the (Unix?) operating system where your files reside. I don't know if the FILESRVC access method supports wildcard characters, though.

Here is an example that works in SAS 9.4 using UTF-8 encoding under Windows:

1    %let file_test = "SICR Testing Sep'21 with new definition.sas";
2    %put %sysfunc(putc(&file_test,$hex200));
22534943522054657374696E6720536570E2809932312077697468206E657720646566696E6974696F6E2E73617322
3
4    %let file_test_new1a=%sysfunc(ktranslate(&file_test,?,'));
5    %put &=file_test_new1a;
FILE_TEST_NEW1A="SICR Testing Sep?21 with new definition.sas"
6
7    %let file_test_new1b=%sysfunc(inputc(%sysfunc(tranwrd(%sysfunc(putc(&file_test,$hex200)),E28099,3F)),$hex200));
8    %put &=file_test_new1b;
FILE_TEST_NEW1B="SICR Testing Sep?21 with new definition.sas"

Note that the apostrophe typed as ' in the first two %LET statements above was in fact the Unicode apostrophe 'E28099'x (and would have been displayed as something like ’ here in the forum). By replacing it with a question mark ('3F'x) -- using either the KTRANSLATE function or the INPUTC/TRANWRD/PUTC combo operating on the hex codes -- the INFILE statement would possibly read all files with names matching the pattern. But obviously you wouldn't expect multiple files whose names differ just in that apostrophe.

[Tom]

Good point.  Make sure you can tell the difference between an apostrophe and the floating comma that Microsoft word likes to replace it with.

data test;
  row+1;
  length string $20;
  string="don't";
  output;
  row+1;
  string=tranwrd(string,"'",'E28099'x);
  output;
run;

Where is what it looks like on my PC-SAS running with UTF-8 support.

Also look for non-breaking spaces, 'A0'X in single byte encoding and 'C2A0'x in Unicode.  Or TAB characters, '09'X.

[stewart_Jardine]

what a fantastic idea! love your out of the box thinking! I finished my day of work ready to give up on this yesterday and having read your message I start today re-inspired!

THANK YOU

[Patrick]

---

@stewart_Jardine wrote:

what a fantastic idea! love your out of the box thinking! I finished my day of work ready to give up on this yesterday and having read your message I start today re-inspired!

---

What @FreelanceReinh proposes is not going to work because what gets generated in the background is a REST call with a EQ filter condition. 

The only way to get around the current bug I can think of is to not use the filename filesrvc engine but generate the Proc HTTP command directly.