Programming the statistical procedures from SAS

proc surveyfreq ods output, need to display output differently, use transpose?

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 14
Accepted Solution

proc surveyfreq ods output, need to display output differently, use transpose?

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


Accepted Solutions
Solution
‎07-25-2013 02:15 PM
Occasional Contributor
Posts: 14

Re: proc surveyfreq ods output, need to display output differently, use transpose?

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


All Replies
Grand Advisor
Posts: 10,052

Re: proc surveyfreq ods output, need to display output differently, use transpose?

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

Solution
‎07-25-2013 02:15 PM
Occasional Contributor
Posts: 14

Re: proc surveyfreq ods output, need to display output differently, use transpose?

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

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