How to set sheet_interval for for ods excel as a new sheet for each variable from proc freq by group

[greesamu]

I have a large data set with results from a survey with almost 100 questions. I'm trying to summarize all the answers with proc freq and use the variable "prog" (indicating which 1 of 5 programs the respondent is in) as a by group. I'm then exporting to excel using ods excel. 

Right now, I can get one group per sheet using sheet_interval = "BYGROUP". This gives me 5 sheets where each sheet has 100 proc freq tables summarizing every single question with just the responses from 1 group. What I actually want is 100 sheets with each sheet containing 5 tables so that I can see all responses from all groups on each sheet. Below is my syntax:

ods excel file="/home/u59328758/My SAS Data/Book1.xlsx";
ods excel options (sheet_interval = "BYGROUP");

proc freq data=EQUITY;
by prog;
run;
ods excel close;

[ballardw]

---

@greesamu wrote:

I have a large data set with results from a survey with almost 100 questions. I'm trying to summarize all the answers with proc freq and use the variable "prog" (indicating which 1 of 5 programs the respondent is in) as a by group. I'm then exporting to excel using ods excel. 

Right now, I can get one group per sheet using sheet_interval = "BYGROUP". This gives me 5 sheets where each sheet has 100 proc freq tables summarizing every single question with just the responses from 1 group. What I actually want is 100 sheets with each sheet containing 5 tables so that I can see all responses from all groups on each sheet. Below is my syntax:

ods excel file="/home/u59328758/My SAS Data/Book1.xlsx";
ods excel options (sheet_interval = "BYGROUP");

proc freq data=EQUITY;
by prog;
run;
ods excel close;

---

I suggest picking a small data set, either one of yours or something like the SASHELP.Class data set, show us that set. Then manually build what you expect the result to look like and show. That way we have a better chance of matching your not exactly obvious requirement.

I suspect that if By group for that variable doesn't work that perhaps your data needs to be completely reshaped so the other variables make up the values of the by group and the Prog provides the values. The exact reshape approach would depend on your data.

[greesamu]

Hopefully the below helps. I want excel sheets organized so each sheet has one survey question with all of the data for that question summarized in 5 proc freq tables so that each table contains data regarding one group. 

[ballardw]

Many users here don't want to download Excel files because of virus potential, others have such things blocked by security software or organization policy. Also if you give us Excel we have to create a SAS data set and due to the non-existent constraints on Excel data cells the result we end up with may not have variables of the same type (numeric or character) and even values.

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the </> icon or attached as text to show exactly what you have and that we can test code against.

[Reeza]

You need to restructure your data such that you can use BY group processing on your "group" variable or you can use a macro to loop through each "group".

[Reeza · Accepted solution]

If your variables are indexed as Q1 to Q100 then using a macro loop is probably an easy solution.

EDIT: wrapped in macro call, not sure if it's required or if a do loop can stand alone now. 

%macro createExcel;

ods excel file="/home/u59328758/My SAS Data/Book1.xlsx";

%do i=1 %to 100;

ods excel options (sheet_interval = "proc" sheet_name = "Q&i" );

proc freq data=EQUITY;
by prog;
table Q&i.;
run;

%end;

ods excel close;

%mend;

%createExcel;

---

@greesamu wrote:

I have a large data set with results from a survey with almost 100 questions. I'm trying to summarize all the answers with proc freq and use the variable "prog" (indicating which 1 of 5 programs the respondent is in) as a by group. I'm then exporting to excel using ods excel. 

Right now, I can get one group per sheet using sheet_interval = "BYGROUP". This gives me 5 sheets where each sheet has 100 proc freq tables summarizing every single question with just the responses from 1 group. What I actually want is 100 sheets with each sheet containing 5 tables so that I can see all responses from all groups on each sheet. Below is my syntax:

ods excel file="/home/u59328758/My SAS Data/Book1.xlsx";
ods excel options (sheet_interval = "BYGROUP");

proc freq data=EQUITY;
by prog;
run;
ods excel close;

---

[greesamu]

OMG, icon, legend, giant brain. Thank you so much ❤️

[ChrisNZ]

Why not use

by QUESTION;

?

[Reeza]

Would require restructuring the data which may be a pain if you have to mix character and numeric variables in one.