topic Re: Export results from proc freq 2 by 2 table to excel in SAS Procedures

Export results from proc freq 2 by 2 table to excel

PaulaC — Tue, 02 Oct 2018 02:47:54 GMT

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

Re: Export results from proc freq 2 by 2 table to excel

Patrick — Tue, 02 Oct 2018 07:05:26 GMT

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

Re: Export results from proc freq 2 by 2 table to excel

PaulaC — Tue, 02 Oct 2018 14:27:42 GMT

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?

Re: Export results from proc freq 2 by 2 table to excel

ballardw — Tue, 02 Oct 2018 14:54:11 GMT

@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";

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

Re: Export results from proc freq 2 by 2 table to excel

Reeza — Tue, 02 Oct 2018 15:01:33 GMT

Here's the link corrected.

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

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

Re: Export results from proc freq 2 by 2 table to excel

PaulaC — Tue, 02 Oct 2018 15:25:12 GMT

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.

Re: Export results from proc freq 2 by 2 table to excel

PaulaC — Tue, 02 Oct 2018 15:26:26 GMT

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.

Re: Export results from proc freq 2 by 2 table to excel

Reeza — Tue, 02 Oct 2018 17:17:22 GMT

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.