Potential bug with ODS EXCEL and PROC FREQ

[Yosef]

I am using SAS 9.4 TS Level 1M5, with X64_10PRO platform.

I have encountered a case when ODS EXCEL will not properly generate an Excel file of PROC FREQ output. When I run the SAS code below, the log prints strange errors (posted farther below). When I try to open the resulting Excel file, I encounter a dialogue box with the message, "Excel cannot open the file 'my_file_name.xlsx' because the file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file." I cannot open that Excel file.

However, SAS generates the correct PROC FREQ output in the Results Viewer without issue.

These conditions, together, seem to be causing the problem with ODS EXCEL and PROC FREQ:

1. The variable to the left of the asterisk in the TABLES statement is numeric
2. That variable has a user-defined format
3. At least one of the formatted values of that variable begins with a dollar sign
4. At least one of the values of that variable whose formatted value begins with a dollar sign is in the contingency table in the PROC FREQ output

The code below uses simple data, but it is similar to the data I worked with on my job. This code consistently produces the same errors I encountered at work:

proc format;
	value custom_format
		  0 = "$0"
		  1 = "$1"
		  2 = "$2"
		  3 = "$3"
		  4 = "$4"
		  5 = "$5"
		  6 = "$6"
		  7 = "$7"
		  8 = "$8"
		  9 = "$9"
		  10 = "$10";
run;

data exp;
	do observation = 1 to 1000;
		new_var = round(10*ranuni(10027),1);
		format new_var custom_format.;
		new_var_2 = round(10*ranuni(10029),1);
		output;
	end;
run;

ODS EXCEL FILE = "my_file_path\my_file_name.xlsx"
	options(embedded_titles='on' sheet_interval='none' sheet_name="Sheet 1");

proc freq data=exp;
	tables new_var*new_var_2;
run;

ODS EXCEL CLOSE;

And this is what the log says, with file paths modified for privacy:

25 ODS EXCEL FILE = "my_file_path\my_file_name.xlsx"

26 options(embedded_titles='on' sheet_interval='none' sheet_name="Sheet 1");

27

28 proc freq data=exp;

29 tables new_var*new_var_2;

30 run;

NOTE: Writing HTML Body file: sashtml.htm

ERROR: In event 'text_run': bad argument #2 to 'format' (string expected, got nil)

stack traceback:

[C]: in function 'format'

?: in function '?'

?: in function <?:508>

(tail call): ?

ERROR: An error occurred during script execution. See the preceding messages.

ERROR: Fatal ODS error has occurred. Unable to continue processing this output destination.

ERROR: Fatal ODS error has occurred. Unable to continue processing this output destination.

NOTE: Writing EXCEL file: my_file_path\my_file_name.xlsx

ERROR: File is in use, C:\Users\Subfolder1\Subfolder2\Local\Temp\SAS Temporary

Files\_TD7288_P003962-DT_\_T000000000C27F900\[Content_Types].xml.

WARNING: An error occurred while deleting the temporary package files in

C:\Users\Subfolder1\Subfolder2\Local\Temp\SAS Temporary

Files\_TD7288_P003962-DT_\_T000000000C27F900.

The file is already locked by another user.

ERROR: Fatal ODS error has occurred. Unable to continue processing this output destination.

NOTE: The SAS System stopped processing this step because of errors.

NOTE: There were 1000 observations read from the data set WORK.EXP.

NOTE: PROCEDURE FREQ used (Total process time):

real time 0.65 seconds

cpu time 0.31 seconds

31

32 ODS EXCEL CLOSE;

The workaround I used on my job was just to reverse the order of the variables in the TABLES statement--the variable I worked with had formatted values unsuitable for the DOLLARw.d format. However, even though I was able to find a workaround, I still thought that I should post this.

[Reeza]

Have you reported this to tech support?

It's why people pay for SAS 🙂

They often help you find a solution or workaround as well.

---

@Yosef wrote:

I am using SAS 9.4 TS Level 1M5, with X64_10PRO platform.

I have encountered a case when ODS EXCEL will not properly generate an Excel file of PROC FREQ output. When I run the SAS code below, the log prints strange errors (posted farther below). When I try to open the resulting Excel file, I encounter a dialogue box with the message, "Excel cannot open the file 'my_file_name.xlsx' because the file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file." I cannot open that Excel file.

However, SAS generates the correct PROC FREQ output in the Results Viewer without issue.

These conditions, together, seem to be causing the problem with ODS EXCEL and PROC FREQ:

1. The variable to the left of the asterisk in the TABLES statement is numeric
2. That variable has a user-defined format
3. At least one of the formatted values of that variable begins with a dollar sign
4. At least one of the values of that variable whose formatted value begins with a dollar sign is in the contingency table in the PROC FREQ output

The code below uses simple data, but it is similar to the data I worked with on my job. This code consistently produces the same errors I encountered at work:

proc format;
	value custom_format
		  0 = "$0"
		  1 = "$1"
		  2 = "$2"
		  3 = "$3"
		  4 = "$4"
		  5 = "$5"
		  6 = "$6"
		  7 = "$7"
		  8 = "$8"
		  9 = "$9"
		  10 = "$10";
run;

data exp;
	do observation = 1 to 1000;
		new_var = round(10*ranuni(10027),1);
		format new_var custom_format.;
		new_var_2 = round(10*ranuni(10029),1);
		output;
	end;
run;

ODS EXCEL FILE = "my_file_path\my_file_name.xlsx"
	options(embedded_titles='on' sheet_interval='none' sheet_name="Sheet 1");

proc freq data=exp;
	tables new_var*new_var_2;
run;

ODS EXCEL CLOSE;

And this is what the log says, with file paths modified for privacy:

25 ODS EXCEL FILE = "my_file_path\my_file_name.xlsx"

26 options(embedded_titles='on' sheet_interval='none' sheet_name="Sheet 1");

27

28 proc freq data=exp;

29 tables new_var*new_var_2;

30 run;

NOTE: Writing HTML Body file: sashtml.htm

ERROR: In event 'text_run': bad argument #2 to 'format' (string expected, got nil)

stack traceback:

[C]: in function 'format'

?: in function '?'

?: in function <?:508>

(tail call): ?

ERROR: An error occurred during script execution. See the preceding messages.

ERROR: Fatal ODS error has occurred. Unable to continue processing this output destination.

ERROR: Fatal ODS error has occurred. Unable to continue processing this output destination.

NOTE: Writing EXCEL file: my_file_path\my_file_name.xlsx

ERROR: File is in use, C:\Users\Subfolder1\Subfolder2\Local\Temp\SAS Temporary

Files\_TD7288_P003962-DT_\_T000000000C27F900\[Content_Types].xml.

WARNING: An error occurred while deleting the temporary package files in

C:\Users\Subfolder1\Subfolder2\Local\Temp\SAS Temporary

Files\_TD7288_P003962-DT_\_T000000000C27F900.

The file is already locked by another user.

ERROR: Fatal ODS error has occurred. Unable to continue processing this output destination.

NOTE: The SAS System stopped processing this step because of errors.

NOTE: There were 1000 observations read from the data set WORK.EXP.

NOTE: PROCEDURE FREQ used (Total process time):

real time 0.65 seconds

cpu time 0.31 seconds

31

32 ODS EXCEL CLOSE;

The workaround I used on my job was just to reverse the order of the variables in the TABLES statement--the variable I worked with had formatted values unsuitable for the DOLLARw.d format. However, even though I was able to find a workaround, I still thought that I should post this.

---

[ballardw · Accepted solution]

Try this:

ODS EXCEL FILE = "my_file_path\my_file_name.xlsx"
	options(embedded_titles='on' sheet_interval='none' sheet_name="Sheet 1");

proc freq data=exp;
	tables new_var*new_var_2;
        format new_var dollar3.;
run;

ODS EXCEL CLOSE;

Excel is known to do some odd things with formatted SAS values occasionaly and apparently you have discovered one more.

Since this code seems to run without the error:

ODS EXCEL FILE = "my_file_path\my_file_name.xlsx" 
   options(embedded_titles='on' sheet_interval='none' sheet_name="Sheet 1"); 

proc freq data=exp; 
   tables new_var*new_var_2 /list; 
run; 
ODS EXCEL CLOSE;

it appears to have something to do with the way that particular format is used in a cell that spans rows created by proc freq. Spanned rows from proc tabulate such as

proc tabulate data=exp; 
    class new_var: ;
   tables new_var*new_var_2 ,n; 
run; 

don't recreate the error either.

[Reeza]

The SAS log generates an error as well though, this isn't just excel in this case.

[Yosef]

Thanks for the response and troubleshooting—the countless posts from you and Reeza on this forum have made my life a lot easier.

The formatted values I was required to use for the variable I was working with were of the form “$10,000 to $20,000”, “$20,000 to $30,000”, and so forth, so the DOLLARw.d format would not have sufficed as a workaround in that case. However, as you say, your workaround in this example indicates that the dollar sign itself is not exactly the root of the problem.

Judging by the responses from you and Reeza, it looks like we have encountered a legitimate case of ODS EXCEL misbehaving. Before I contacted tech support about this, I wanted to be relatively certain that the errors did not stem from a lack of understanding on my part.

[ballardw]

---

@Yosef wrote:

Thanks for the response and troubleshooting—the countless posts from you and Reeza on this forum have made my life a lot easier.

The formatted values I was required to use for the variable I was working with were of the form “$10,000 to $20,000”, “$20,000 to $30,000”, and so forth, so the DOLLARw.d format would not have sufficed as a workaround in that case. However, as you say, your workaround in this example indicates that the dollar sign itself is not exactly the root of the problem.

Judging by the responses from you and Reeza, it looks like we have encountered a legitimate case of ODS EXCEL misbehaving. Before I contacted tech support about this, I wanted to be relatively certain that the errors did not stem from a lack of understanding on my part.

---

Without seeing exactly what you attempted and the values I have to suspect that you may not have used a wide enough dollar format.

data example;
   input x;
datalines;
1
12
123
1234
12345
123456
1234567
12345678
123456789
;
run;

proc print data=example;
   format x dollar14.;
run;

You need to make sure that you account for the extra spaces occupied by the $, the expected maximum number of commas, a decimal and the decimal places requested. If you don't provide enough spaces the commas will be omitted trying to fit as much as practical into the result.

proc print data=example;
   format x dollar10.;
run;

which starts removing commas with 8 digits to display because the required 2 commas, the $ and the 8 digits = 11 characters but you requested only 10 with the format.

If you get really stingy with not allowing enough characters then you get unexpected by new SAS user results such as:

proc print data=example;
   format x dollar6.;
run;

Which shows one value with a comma in the example data set, the next a $ without commas, the next no $ sign and after that no $ and the numeric portion is in exponential format.

Moral of the story: Use enough spaces. And if you are summing values such as in Proc report, you likely need to make the format even wider so the sum  will display correctly.

[Yosef]

No, with the formatted values I was using, even the format “DOLLAR32.” would not have worked. I had to mimic the appearance of a PDF the researcher I was collaborating with was using. For instance, one of the formatted values—the exact string (inside the double quotation marks)—was “$50,000 to $74,999”. Another formatted value I had to use was literally “$100,000 or more”.

I used much simpler formatted values than those in my original post because I was trying to narrow down what the factors are that, together, reproduce the errors in the log. My boss and I initially suspected that the problem was caused by some kind of cell width issue in Excel, but as my original post implies (where the maximum string-length out of all the formatted values of that variable is 3), that is not the case.

And as your first post indicates, you were able to sidestep the errors by using the LIST option in PROC FREQ, further narrowing down what causes the errors.