BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
PaulaC
Fluorite | Level 6

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

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

@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

View solution in original post

7 REPLIES 7
PaulaC
Fluorite | Level 6
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
Super User

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
Fluorite | Level 6
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
Super User

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
Super User

@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
Fluorite | Level 6
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.

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

What is Bayesian Analysis?

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 7 replies
  • 15939 views
  • 1 like
  • 4 in conversation