Underscore in file path leading to invalid file in ODS Excel file=

[Jacob]

Hi all--

I'm trying to write the result of a query to an Excel workbook, but the path I'm using has an underscore (_) in it which is causing the program to error out.  The file path is:

\\Opr.companyname.org\dfs\BANK\00\WORKGROUP\BANK\FCRA_CO\SASoutputsCRCMS\

This is how it resolves in my execution and the error:

ERROR: Invalid file, \\OPR.COMPANYNAME.ORG\DFS\BANK\00\WORKGROUP\BANK\FCR.

Like it is turning the "A_" into a "." and stops processing.

This is on a Windows 10 machine.  I've tried using %str(_) and %bquote, but haven't had any luck. the file will be named &Report_Mth..xlsx.

Can one of the brilliant minds out there help me get past this?  Thank you,

[SASKiwi]

Please post your SAS log. Bit hard to provide a fix for a program we can't see.

[Tom]

Underscores are not normally an issue.  Are you sure you have an actual underscore (hex code 5F) and not some strange UTF-8 symbol that looks like an underscore?

If it is really an underscore please provide more context.  So the lines from the SAS log that show the code run and the error it generates.

Note that there certainly used to be filename length limits in Windows such that it was sometimes necessary to create drive letter mappings to long network paths so that you could reference the file without exceeding the limit.  It seems to me the limit was 256 bytes. 

[Jacob]

Below is my log for the most recent run I had.

32 %LET path1=\\Opr.companyname.org\dfs\BANK\00\WORKGROUP\BANK\;
33 %LET path2=FCRA_CO\SASoutputsCRCMS\;
34
35 %Put NOTE: Report month = &Report_Mth;
NOTE: Report month = 29Feb2020

38
39
40 ODS TAGSETS.EXCELXP
41 file="&path1.&path2.&Report_Mth..xlsx"
42 STYLE=minimal
43 OPTIONS (Orientation = 'landscape'
44 FitToPage = 'yes'
45 Pages_FitWidth = '1'
46 Pages_FitHeight = '100'
47 embeded_titles = "Yes");
2 The SAS System 14:00 Thursday, April 16, 2020

NOTE: Writing TAGSETS.EXCELXP Body file: \\Opr.companyname.org\dfs\BANK\00\WORKGROUP\BANK\FCRA_CO\SASoutputsCRCMS\29Feb2020.xlsx
ERROR: Invalid file, \\OPR.COMPANYNAME.ORG\DFS\BANK\00\WORKGROUP\BANK\FCR.
ERROR: No body file. TAGSETS.EXCELXP output will not be created.
48
49 Title 'Approved Consent Prior to App Submission';
50 PROC SQL;
51 CREATE TABLE CO_Data AS
52 SELECT *
53 FROM Work.CC_Unq_Out
54
55 UNION
56
57 SELECT *
58 FROM Work._2_VEH_APPS;
WARNING: A table has been extended with null columns to perform the UNION set operation.
NOTE: Table WORK.CO_DATA created, with 4 rows and 4 columns.

59 QUIT;

[Tom · Accepted solution]

Perhaps it is just truncated in the error message?  Does that folder actually exist?  Try working your way down the paths one level at a time and make sure each sub folder actually exists. 

Try writing to a temporary file and see if that works. You can always add a step later to copy the file to the real location. 

Is it just a bug in that particular tagsets code? How old is your version of SAS? Have you tried to the new ODS EXCEL destination instead? That will write a real XLSX file. Does it work for your report? Does it generate the same error if you try to write directly to that path?

[Jacob]

I’m running EG 9.4 I haven’t tried the new ODS EXCEL. I’ll give that a go. I’ll also step through each sub folder until I get an error. I’ll post back tomorrow morning with updates.

Thank you!

[SASKiwi]

Does it work if you hard code the FILE= option on your ODS statement instead of using macro variables? If it still fails then this isn't a macro resolution problem. 

[Jacob]

I ended up using an Export Task as a step in the process.  

I tried hard coding the path and with no macro variables to no success.  I also switched to ODS Excel for ODS tagset.Excel and would receive an error of valid path and then my workstation alias and the output file name (i.e. SPAL.CO_DATA.xlsx).  I tried writing a proc export program to place it in a temp location and then do the Copy Files task in EG to my desired destination and still no luck.  

While it is not the ideal, it gets me what I need.  Not sure what the issue is, but as this process has a finite life, I'm not going to put too much into figuring it out.  

Thank you for all your replies and help.