Export results from proc freq 2 by 2 table to excel

[PaulaC]

I would like to export the results (each 2 by 2 table) from proc freq to excel.   I have 29 variables in my proc freq tables statement.  Each variable will produce a 2 by 2 table with study type status.  For example: age*study.

I initially tried using the out statement option in the table statement and then was going to export to Excel. Unfortunately only the results for the last variable in my list got output.  I feel that this should be an easy fix, but have not been able to find an answer online.

My code is as follows:

proc freq data=data;
tables sex*study race*study countrygrp*study totcomorbid*study risk*study totmetsites*study metloc*study visceral*study 
nonvisceral*study breast*study skin*study bone*study liver*study lung*study lymphnode*study othersite*study priortrt*study 
priorrx*study chemotherapy*study surgery*study radiotherapy*study regradio*study metradio*study hormonal*study bestresp*study 
suppcare*study whob*study arm*study numpt*study/norow nopercent out=freq;
where exclude =. & visn = 1;
run;

Any help you could provide would be greatly appreciated.

Thanks,

Paula

[Patrick]

https://communities.sas.com/t5/SAS-Procedures/How-to-output-multiple-variables-from-proc-freq-into-a...

[PaulaC]

Thank you for the response, but unfortunately the link did not work. Would you be able to send me the complete title so I can search for it?

[Reeza]

Here's the link corrected.

https://communities.sas.com/t5/SAS-Procedures/How-to-output-multiple-variables-from-proc-freq-into-a...

And here's another option, it's not particularly efficient but I like this macro. Run this as a demo (assuming your input data set is really called data) and then check the output data set, which is in summary_demo in the work library.

%let source_path =https://gist.githubusercontent.com/statgeek/0ff46bbd76bcc101e40945816b232303/raw/712547f2e6feb20858322948c7a163237cb341e7/summary_table.sas;

filename reprex url "&source_path";
%include reprex;
filename reprex;


%let var_demo = sex race countrygrp;
%summary_table(dsetin = data, 
            dsetout = summary_demo, 
            varlist = &var_demo, 
            varcross = study, 
            rc = col, 
            missing=, 
            replace=Y, 
            nlevel_cutoff=25);

[PaulaC]

Thank you for the information. Would you be able to explain the steps in the macro for me? I am not 100% clear on how it is working.

[Reeza]

You can see the code, go to the linked path in source path. It's basically a PROC FREQ loop that appends all the results together in a somewhat nicely formatted table with the N (##%) values formatted. 

[ballardw · Accepted solution]

---

@PaulaC wrote:

I would like to export the results (each 2 by 2 table) from proc freq to excel.   I have 29 variables in my proc freq tables statement.  Each variable will produce a 2 by 2 table with study type status.  For example: age*study.

I initially tried using the out statement option in the table statement and then was going to export to Excel. Unfortunately only the results for the last variable in my list got output.  I feel that this should be an easy fix, but have not been able to find an answer online.

My code is as follows:

proc freq data=data;
tables sex*study race*study countrygrp*study totcomorbid*study risk*study totmetsites*study metloc*study visceral*study 
nonvisceral*study breast*study skin*study bone*study liver*study lung*study lymphnode*study othersite*study priortrt*study 
priorrx*study chemotherapy*study surgery*study radiotherapy*study regradio*study metradio*study hormonal*study bestresp*study 
suppcare*study whob*study arm*study numpt*study/norow nopercent out=freq;
where exclude =. & visn = 1;
run;

Any help you could provide would be greatly appreciated.

Thanks,

Paula

---

ods excel file="<path>\filename.xlsx";

<proc freq code goes here>

ods excel close;

would be one way where <path> is the path to the desired output location such as c:\projectfolder or similar. There are options for ODS EXCEL to have some control over one tab per table or one tab for the procedure and such.

Are you familiar with the notation in Proc Freq that shows how to cross groups of variables?

You can simplify the code for a tables statement by using parentheses and the * For example

nonvisceral*study breast*study skin*study bone*study liver*study lung*study lymphnode*study othersite*study priortrt*study 

could instead be

(nonvisceral breast skin bone liver lung ymphnode othersite priortrt)* study

[PaulaC]

Thank you for the information and I did not know about the code simplification. Thank you for that. I will try the code you provided.