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

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.


sas_question_figure1_table.PNG

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?

sas_question_figure2_desired_layout.PNG

1 ACCEPTED SOLUTION

Accepted Solutions
Bautista
Calcite | Level 5

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.

matrix.PNG

%macro mergetables(indepvariable,outcomevariable,out_tablename,maxrowvals,maxcolvals,tablenum);

%let currentcolval=1;

%do currentcolval = 1 %to &maxcolvals ;

  data merged_col&currentcolval&outcomevariable;

  %let string2 = &outcomevariable&currentcolval;

  set %do currentrow=1 %to %eval(&maxrowvals +1); &string2&currentrow %end; ;

  run;

  proc datasets;

  delete %do currentrow=1 %to %eval(&maxrowvals +1); &string2&currentrow %end; ;

  run;

%end;

%let currentcolval=1;

  data pre&tablenum&outcomevariable&indepvariable;

  merge %do currentcolval=1 %to  &maxcolvals ; merged_col&currentcolval&outcomevariable %end; ;

  by F_&indepvariable;

  run;

  proc datasets;

  delete %do currentcolval=1 %to &maxcolvals ; merged_col&currentcolval&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&currentrowval %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&currentrowval %end; ;

run;

%mend mergetables;

/*   Call the macro    */

%mergetables(raceethnicity,response,T_01_psfd_racexresponse,4,5,T_01);

View solution in original post

2 REPLIES 2
ballardw
Super User

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

Bautista
Calcite | Level 5

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.

matrix.PNG

%macro mergetables(indepvariable,outcomevariable,out_tablename,maxrowvals,maxcolvals,tablenum);

%let currentcolval=1;

%do currentcolval = 1 %to &maxcolvals ;

  data merged_col&currentcolval&outcomevariable;

  %let string2 = &outcomevariable&currentcolval;

  set %do currentrow=1 %to %eval(&maxrowvals +1); &string2&currentrow %end; ;

  run;

  proc datasets;

  delete %do currentrow=1 %to %eval(&maxrowvals +1); &string2&currentrow %end; ;

  run;

%end;

%let currentcolval=1;

  data pre&tablenum&outcomevariable&indepvariable;

  merge %do currentcolval=1 %to  &maxcolvals ; merged_col&currentcolval&outcomevariable %end; ;

  by F_&indepvariable;

  run;

  proc datasets;

  delete %do currentcolval=1 %to &maxcolvals ; merged_col&currentcolval&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&currentrowval %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&currentrowval %end; ;

run;

%mend mergetables;

/*   Call the macro    */

%mergetables(raceethnicity,response,T_01_psfd_racexresponse,4,5,T_01);

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is ANOVA?

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.

Discussion stats
  • 2 replies
  • 4117 views
  • 0 likes
  • 2 in conversation