topic Different data sets in multiple excel sheet in ODS and Base Reporting

Different data sets in multiple excel sheet

dustychair — Mon, 14 Jun 2021 18:02:15 GMT

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

Re: Different data sets in multiple excel sheet

Athenkosi — Mon, 14 Jun 2021 18:53:50 GMT

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?

Re: Different data sets in multiple excel sheet

dustychair — Mon, 14 Jun 2021 18:59:40 GMT

Results are saved in multiple datasets.

Re: Different data sets in multiple excel sheet

Reeza — Mon, 14 Jun 2021 19:01:06 GMT

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

Re: Different data sets in multiple excel sheet

dustychair — Mon, 14 Jun 2021 23:47:01 GMT

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);

Re: Different data sets in multiple excel sheet

Reeza — Tue, 15 Jun 2021 14:28:34 GMT

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

Re: Different data sets in multiple excel sheet

dustychair — Tue, 15 Jun 2021 16:56:51 GMT

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

Re: Different data sets in multiple excel sheet

Reeza — Tue, 15 Jun 2021 17:15:30 GMT

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.