@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.
