topic proc surveyfreq ods output, need to display output differently, use transpose? in Statistical Procedures

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

Bautista — Fri, 07 Jun 2013 01:22:01 GMT

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?

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

ballardw — Fri, 07 Jun 2013 15:46:39 GMT

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;

ods output close;

It looks like proc tabulate would generate the output you desire excuding the rows with the summaries (response= .);

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

Bautista — Thu, 25 Jul 2013 18:15:04 GMT

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