topic Re: Using ods to copy proc freq result tables to Excel in SAS Programming

Using ods to copy proc freq result tables to Excel

wlierman — Tue, 04 Feb 2025 21:43:22 GMT

I have 10 FY of dental procedure data to send to a university researcher.

There are well over a million records for each year. I am using the proc freq procedure to produce a table of procedure counts by age groups.

Data ANALYZE5.Dental_CAPSTONE_FY2015_1_AgeGrp;
   length Age_group $14.; 
   Set ANALYZE5.Dental_CAPSTONE_Summary_FY2015;
If   18 <= Recipient_Age <= 25 then Age_group = '18-25';
Else If 26 <= Recipient_Age <= 35 then Age_group = '26-35';
Else If 36 <= Recipient_Age <= 45 then Age_group = '36-45';
Else If 46 <= Recipient_Age <= 55 then Age_group = '46-55';
Else If 56 <= Recipient_Age <= 65 then Age_group = '56-65';
Else If 66 <= Recipient_Age <= 752 then Age_group = '66-75';
Else If 76 <= Recipient_Age <= 85 then Age_group = '76-85';
Else If 85 <= Recipient_Age then Age_group = '85 and ';
run;


ods tagsets.excelxp 
file="H:\Univ_Alaska_Anchorage_data_DSSURS\FreqTable.xlsx" style=Printer options (Orientation = 'landscape');


proc freq data = ANALYZE5.Dental_CAPSTONE_FY2015_1_AgeGrp;
tables Procedure_Code___Desc * Age_group / nopercent norow nocol;

/*ods output FreqTable = Freq_Results_FY2015_1;*/
run;

ods tagsets.excelxp close;

When I run the code from the ods tagsets.excelxp line, the following error is produced

WARNING: Output 'FreqTable' was not created. Make sure that the output object name, label, or path is spelled correctly. Also, verify that the appropriate procedure options are used to produce the requested output object. For example, verify that the NOPRINT option is not used.

And in the folder where the output is to be sent has the following:

Excel cannot open the file 'FreqTable.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 appreciate all the help that comes from this group. Thank you in advance.

wlierman

Re: Using ods to copy proc freq result tables to Excel

PaigeMiller — Tue, 04 Feb 2025 21:48:18 GMT

How about this:

ods excel file="test.xlsx";
proc freq data=sashelp.class;
tables age;
run;
ods excel close;

Re: Using ods to copy proc freq result tables to Excel

ChrisHemedinger — Tue, 04 Feb 2025 21:53:58 GMT

As @PaigeMiller suggests, use ODS Excel always and not ODS tagsets.ExcelXP. ODS Excel will create a native XLSX file that Excel will happily open. The old tagsets method creates an XML-based monster file that Excel no longer tolerates.

Re: Using ods to copy proc freq result tables to Excel

SASKiwi — Tue, 04 Feb 2025 22:22:14 GMT

If you want to continue using EXCELXP (which is still a good choice if you have an old SAS 9.4 maintenance level) then write to an XML file. This works with current versions of Excel in my experience:

ods tagsets.excelxp 
file="H:\Univ_Alaska_Anchorage_data_DSSURS\FreqTable.xml" style=Printer options (Orientation = 'landscape');

ODS EXCEL only became reliable from around 9.4M4 and higher.

Re: Using ods to copy proc freq result tables to Excel

wlierman — Wed, 05 Feb 2025 00:11:32 GMT

I have followed your suggestion and it provides a solution to my proble.
Thank you.
wlierman

Re: Using ods to copy proc freq result tables to Excel

ballardw — Wed, 05 Feb 2025 06:02:41 GMT

@wlierman wrote:

I have 10 FY of dental procedure data to send to a university researcher.

There are well over a million records for each year. I am using the proc freq procedure to produce a table of procedure counts by age groups.
Data ANALYZE5.Dental_CAPSTONE_FY2015_1_AgeGrp;
   length Age_group $14.; 
   Set ANALYZE5.Dental_CAPSTONE_Summary_FY2015;
If   18 <= Recipient_Age <= 25 then Age_group = '18-25';
Else If 26 <= Recipient_Age <= 35 then Age_group = '26-35';
Else If 36 <= Recipient_Age <= 45 then Age_group = '36-45';
Else If 46 <= Recipient_Age <= 55 then Age_group = '46-55';
Else If 56 <= Recipient_Age <= 65 then Age_group = '56-65';
Else If 66 <= Recipient_Age <= 752 then Age_group = '66-75';
Else If 76 <= Recipient_Age <= 85 then Age_group = '76-85';
Else If 85 <= Recipient_Age then Age_group = '85 and ';
run;


ods tagsets.excelxp 
file="H:\Univ_Alaska_Anchorage_data_DSSURS\FreqTable.xlsx" style=Printer options (Orientation = 'landscape');


proc freq data = ANALYZE5.Dental_CAPSTONE_FY2015_1_AgeGrp;
tables Procedure_Code___Desc * Age_group / nopercent norow nocol;

/*ods output FreqTable = Freq_Results_FY2015_1;*/
run;

ods tagsets.excelxp close;
When I run the code from the ods tagsets.excelxp line, the following error is produced

WARNING: Output 'FreqTable' was not created. Make sure that the output object name, label, or path is spelled correctly. Also, verify that the appropriate procedure options are used to produce the requested output object. For example, verify that the NOPRINT option is not used.

And in the folder where the output is to be sent has the following:

Excel cannot open the file 'FreqTable.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 appreciate all the help that comes from this group. Thank you in advance.

wlierman

The part I color highlighted is telling you that the "object" Freqtable is the problem. I am assuming the warning is from a run where the ODS OUTPUT statement was not commented.

With Proc freq and ODS output you have two possible "frequency" table names: ONEWAYFREQS and CROSSTABFREQS. You can check the name of tables created in the online help Details tab under ODS Table Names. Note there is no "FREQTABLE" anywhere.

If in question using ODS TRACE to get the names of the created output objects to use in a correct ODS OUTPUT option. Hint: with large data sets use the dataset option OBS=10 to generate the results quickly.

Example that you can run using the SAS supplied SASHELP.CLASS data set:

ods trace on;

proc freq data=sashelp.class;
tables sex*age /nopercent norow nocol;
run;

ods trace off;

The Trace on and off start and end instructions to send trace output to the LOG. The result from the above, which mimics your options for Proc Freq shows this as the TRACE output:

Output Added:
-------------
Name:       CrossTabFreqs
Label:      Cross-Tabular Freq Table
Template:   Base.Freq.CrossTabFreqs
Path:       Freq.Table1.CrossTabFreqs
-------------

Which means the only "table" is the Crosstabfreqs.

To create the output data set using that approach would look like:

proc freq data=sashelp.class;
   tables sex*age /nopercent norow nocol;
   ods output crosstabfreqs= Freq_result;
run;

However that data set might not be as "nice" as you would like for output but that is the approach and syntax used correctly.

The Trace information can bet a bit complex to match to desired data sets with the number of options that Proc Freq has coupled with the possibility of multiple TABLES statements in a single Procedure call.