I have a dataset with a weight variable (as well as cluster and strata variables) as shown below.
proc sort data=mydataset;
by response;
run;
proc surveyfreq data= mydataset;
tables raceethnicity * response / cl row ;
strata studystratum;
cluster studycluster;
weight surveyweight ;
ods output crosstabs=tableoutput ;
run;
When I run proc surveyfreq with ods for output, I get the following type of output.
However, eventually I will need to display my data as shown below. I already know how to export into Excel the ODS output from proc surveyfreq, and then I can manually copy-and-paste cells to re-order them into this desired layout but that would take forever (and would be prone to lots of keystroke mistakes). I want a simple way to do this in SAS. If a macro is required, that is OK because I am familiar with macros and arrays. But before I try doing it with a macro, I think there is probably another easier option, yes?
Updating my post.
I was able to figure-out a solution for this problem, using a macro which pulls specific cells from the proc surveyfreq ods output table.
It took me a long tie to create the macro, but eventually I got it done.
In the example above, there are 4 possible values for the independent predictor variable "RaceEthnicity" and 5 possible values for the dependent/outcome variable "response."
So in order to create my formatted table, I needed to first understand the following logic. After I did that, it was easy to create a macro based on a series of do/to/end statements.
%macro mergetables(indepvariable,outcomevariable,out_tablename,maxrowvals,maxcolvals,tablenum); %let currentcolval=1; %do currentcolval = 1 %to &maxcolvals ; data merged_col¤tcolval&outcomevariable; %let string2 = &outcomevariable¤tcolval; set %do currentrow=1 %to %eval(&maxrowvals +1); &string2¤trow %end; ; run; proc datasets; delete %do currentrow=1 %to %eval(&maxrowvals +1); &string2¤trow %end; ; run; %end; %let currentcolval=1; data pre&tablenum&outcomevariable&indepvariable; merge %do currentcolval=1 %to &maxcolvals ; merged_col¤tcolval&outcomevariable %end; ; by F_&indepvariable; run; proc datasets; delete %do currentcolval=1 %to &maxcolvals ; merged_col¤tcolval&outcomevariable %end; ; run; %let currentrowval=1; data &tablenum&outcomevariable&indepvariable; merge pre&tablenum&outcomevariable&indepvariable %do currentrowval=1 %to %eval(&maxrowvals +1); For_n_of_row¤trowval %end; ; by F_&indepvariable; run; /* EXPORT THE DATASET '&tablenum&outcomevariable&indepvariable' saved as '&tablenum&out_tablename' */ PROC EXPORT DATA=WORK.&tablenum&outcomevariable&indepvariable OUTFILE= "C:\Documents and Settings\user\Desktop\save_exports\&tablenum&out_tablename" DBMS=EXCEL5 REPLACE; RUN; /* EXPORT THE DATASET of proc surveyfreq statistics '&out_tablename' */ PROC EXPORT DATA=WORK.&out_tablename OUTFILE= "C:\Documents and Settings\gjbautista\Desktop\save_exports\psfd\&tablenum&out_tablename" DBMS=EXCEL5 REPLACE; RUN; %let currentrowval=1; proc datasets; delete pre&tablenum&outcomevariable&indepvariable %do currentrowval=1 %to %eval(&maxrowvals +1); For_n_of_row¤trowval %end; ; run; %mend mergetables; /* Call the macro */ %mergetables(raceethnicity,response,T_01_psfd_racexresponse,4,5,T_01); |
Since SAS has decided to not add output datasets options to the survey procs you can use the ODS Output statement to bring the results into a dataset that you can then manipulate.
In this case I think you want
ods output crosstabs=mylib.mycrosstabdata;
<surveyfreq code>
ods output close;
It looks like proc tabulate would generate the output you desire excuding the rows with the summaries (response= .);
Updating my post.
I was able to figure-out a solution for this problem, using a macro which pulls specific cells from the proc surveyfreq ods output table.
It took me a long tie to create the macro, but eventually I got it done.
In the example above, there are 4 possible values for the independent predictor variable "RaceEthnicity" and 5 possible values for the dependent/outcome variable "response."
So in order to create my formatted table, I needed to first understand the following logic. After I did that, it was easy to create a macro based on a series of do/to/end statements.
%macro mergetables(indepvariable,outcomevariable,out_tablename,maxrowvals,maxcolvals,tablenum); %let currentcolval=1; %do currentcolval = 1 %to &maxcolvals ; data merged_col¤tcolval&outcomevariable; %let string2 = &outcomevariable¤tcolval; set %do currentrow=1 %to %eval(&maxrowvals +1); &string2¤trow %end; ; run; proc datasets; delete %do currentrow=1 %to %eval(&maxrowvals +1); &string2¤trow %end; ; run; %end; %let currentcolval=1; data pre&tablenum&outcomevariable&indepvariable; merge %do currentcolval=1 %to &maxcolvals ; merged_col¤tcolval&outcomevariable %end; ; by F_&indepvariable; run; proc datasets; delete %do currentcolval=1 %to &maxcolvals ; merged_col¤tcolval&outcomevariable %end; ; run; %let currentrowval=1; data &tablenum&outcomevariable&indepvariable; merge pre&tablenum&outcomevariable&indepvariable %do currentrowval=1 %to %eval(&maxrowvals +1); For_n_of_row¤trowval %end; ; by F_&indepvariable; run; /* EXPORT THE DATASET '&tablenum&outcomevariable&indepvariable' saved as '&tablenum&out_tablename' */ PROC EXPORT DATA=WORK.&tablenum&outcomevariable&indepvariable OUTFILE= "C:\Documents and Settings\user\Desktop\save_exports\&tablenum&out_tablename" DBMS=EXCEL5 REPLACE; RUN; /* EXPORT THE DATASET of proc surveyfreq statistics '&out_tablename' */ PROC EXPORT DATA=WORK.&out_tablename OUTFILE= "C:\Documents and Settings\gjbautista\Desktop\save_exports\psfd\&tablenum&out_tablename" DBMS=EXCEL5 REPLACE; RUN; %let currentrowval=1; proc datasets; delete pre&tablenum&outcomevariable&indepvariable %do currentrowval=1 %to %eval(&maxrowvals +1); For_n_of_row¤trowval %end; ; run; %mend mergetables; /* Call the macro */ %mergetables(raceethnicity,response,T_01_psfd_racexresponse,4,5,T_01); |
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!
ANOVA, or Analysis Of Variance, is used to compare the averages or means of two or more populations to better understand how they differ. Watch this tutorial for more.
Find more tutorials on the SAS Users YouTube channel.