Different data sets in multiple excel sheet

[dustychair]

Hi all,

I have multiple result sets that I saved using OneWayFreqs and I want to export them to one excel file with multiple sheets. Could you help me with that?

Thanks

[Athenkosi]

Hello,

Before I give you a solution I just want to make sure that I understand your problem. So your results sets are stored in multiple dataset or in one?

[dustychair]

Results are saved in multiple datasets.

[Reeza · Accepted solution]

Quickest way is to wrap your code in ODS EXCEL.

ods excel file="/home/reeza/demo/myfiles.xlsx" options(sheet_interval='proc') style=meadow;

proc freq data=sashelp.class;
table sex*age;
run;

proc freq data=sashelp.cars;
table origin*cylinders;
run;

ods excel close;

---

@dustychair wrote:

Hi all,

I have multiple result sets that I saved using OneWayFreqs and I want to export them to one excel file with multiple sheets. Could you help me with that?

Thanks

---

[dustychair]

Thank you so much for taking care of my question. This code is doing something weird. It is saving them in the excel file but the name of the file and content of the file doesn't match. For example file's name is comprehension but there are literacy results inside. You can find what I have done. 

%macro intd (s);
ods excel file="C:\Projects\pl_by_cluster_&s..xlsx" options(sheet_interval='proc') style=meadow;

data a501_&s._main_inc;
set a501_&s._main_inc;
&s._pl_n=int(&s._pl);
run;

proc sort data=a501_&s._main_inc;
by cluster_&s._n;
run;

proc freq data=a501_&s._main_inc;
table &s._pl_n;
by cluster_&s._n;
ods output OneWayFreqs=pl_by_cluster_&s.
run;

proc sort data=a501_&s._main_inc;
by grade;
run;

proc freq data=a501_&s._main_inc;
table &s._pl_n;
by grade;
ods output OneWayFreqs=pl_by_grade_&s.
run;
ods excel close;
%mend;
%intd (comprehension);
%intd (listening);
%intd (reading);
%intd (speaking);
%intd (writing);
%intd (oral);
%intd (literacy);
%intd (comprehension);
%intd (overall);

[Reeza]

You have comprehension listed twice....
Run the macro with "options mprint symbolgen;" before the code and then check your log.

[dustychair]

@Reeza Thank you so much! I noticed that I missed two semicolons besides writing Comprehension twice. Last question: how can make percentages one digit instead of two using ods excel?

Thank you!

[Reeza]

PROC FREQ doesn't let you control that unfortunately. If you want to control your output you can switch to PROC TABULATE instead - but that also means you need to specify more options to get the same output.