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
@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
Here's the link corrected.
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);
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.
@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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: