ODS Excel in proc freq assign sheet name to variable name or label

[ghosh]

Is there an option to assign the Excel sheet name to the variable name in a proc freq.  For example, in this case I would like to assign the sheet  names Make, Type, Origin,  Drivetrain, respectively.

Even better, if the sheet names can be assigned the variable labels

ods listing close;
ods excel file='report.xlsx'
 options(sheet_interval="table");
proc freq data=sashelp.cars;
 table make type origin drivetrain;
run;
ods excel close;
ods listing;

thanks!

[Reeza]

I don't believe there's an easy way with this type of code. If you use a BY statement (is restructuring the data an option?) then you can use #BYLABEL and #BYVAL in the Sheet_Name option,

[ghosh]

Thanks Reeza, I am just doing an exploratory review of a massive dataset with many classification variables.

My other option is create a macro and call it for each variable in its own proc freq perhaps using loop or a dataset driven call execute.  This way I can set the sheet name manually 

[ballardw]

---

@ghosh wrote:

Thanks Reeza, I am just doing an exploratory review of a massive dataset with many classification variables.

My other option is create a macro and call it for each variable in its own proc freq perhaps using loop or a dataset driven call execute.  This way I can set the sheet name manually 

---

If the data is truly "massive" you may run into other issues. Microsoft claims that the number of sheets in a single workbook is "limited by available memory" without much detail that a casual search reveals. If you have lots of variables and lots of values you may run into that limit.

My first stab at something like this would be to look at the Proc Freq NLEVELS information only. If you have variables that are unique per record, or nearly so, you might well want to exclude them from any such detailed output. Consider any variables that may have 10,000 plus values. How much time are you going to spend reading those sheets? What will you be looking for if you do?

[ghosh]

Thanks for your response @ballardw I will look into the nlevels option for future use.

I was already able to create the report with my attached, however, the issue was getting the worksheets named with the variable.  So what I have done now and it's working the way I want it, is what I had described in my reply to Reeza: a data driven macro using call execute.  I named the sheets using this line

ods excel option(sheet_name="&var");

I am sure it's very inefficient since it took over an hour to run for about 22 variables (instead of  <10 mins) each with a proc freq call. But, it's a one time job to share the data contents in terms of available groupings