topic Formatting Crosstabs in SAS Procedures

Formatting Crosstabs

RobertNYC — Mon, 20 Feb 2012 16:52:46 GMT

Hi all--

I have a series of crosstab tables I would like to format for a report. All I want is to is change the font,add % signs, add titles for all the questions and sections and add page numbers. I have been trying to figure out proc template but I’m been having trouble.

This is an example of what I'm trying to format.

SECTION ONE

How long have you lived XYZ?

procfreq data=work.data;

tablesOffice*Q1 / norow nocol;

run;

Do you like to read?

procfreq data=work.data;

tablesOffice*Q2 / norow nocol;

run;

SECTION TWO

Do you like to play chess?

procfreq data=work.data;

tablesOffice*Q3 / norow nocol;

run;

What is your favorite restaurant?

procfreq data=work.data;

tablesOffice*Q4 / norow nocol;

run;

Here is an example of the code Ihave being experimenting with:

proctemplate;

definecrosstabs Base.Freq.CrossTabFreqs;

cell_style={font=fonts('Arial Narrow') foreground= whatever };

row_total_style={foreground=whatever};

col_total_style={foreground=whatever };

grand_total_style={foreground=whatever };

end;

odslisting close;

odshtml file=test.pdf;

Any help is greatly appreciated.

Thanks.

Formatting Crosstabs

art297 — Mon, 20 Feb 2012 17:17:10 GMT

Have you taken a look at: http://support.sas.com/resources/papers/freq92.pdf ?

It shows how to do much, if not all, of the things you want to do.

Formatting Crosstabs

RobertNYC — Mon, 20 Feb 2012 17:23:59 GMT

Yes, I have. That's exactly what I was looking at. If you notice the example code I'm useing, its from that paper. I'm still have trouble figureing it out.

Formatting Crosstabs

art297 — Mon, 20 Feb 2012 17:49:07 GMT

Not sure I know everything that you want to change. Is the following at least close?

proc format;

picture pctfmt (round) other='009.9%';

run;

proc template;

define crosstabs Base.Freq.CrossTabFreqs;

define header myheader;

text 'How long have you lived XYZ?';

end;

cellvalue frequency percent rowpercent colpercent;

define frequency;

format=8.;

header='Count';

end;

define percent;

format=pctfmt.;

header='Overall %';

end;

define rowpercent;

format=pctfmt.;

header='Row %';

end;

define colpercent;

format=pctfmt.;

header='Col %';

end;

run;

ods listing close;

ods html file='body.html';

title 'SECTION ONE';

proc freq data=sashelp.class;

tables sex*age/ norow nocol;

run;

ods html close;

ods listing;

Formatting Crosstabs

RobertNYC — Mon, 20 Feb 2012 21:00:10 GMT

Hi Art-

this is great, thanks so much. I am away from my computer so I can't test this out. I do have a few questions.

Is "myheader" a statement?

Do I repeat this process with every crosstab for every question I want to run frequencies for? Since every question has a different title and the ODS statment only has one crosstab, I'm a little confused.

How do I change the font?

Thanks so much.

Re: Formatting Crosstabs

art297 — Mon, 20 Feb 2012 21:12:26 GMT

It is defined, among other places, at: http://www.pharmasug.org/proceedings/2011/SAS/PharmaSUG-2011-SAS-HW01.pdf

Rather than alter then original "header" it creates a new header called "myheader"

You can change the font by adding a style statement. I think there were a number of such examples in the original reference.

If they aren't clear, restate the question using sashelp.class as the data and the code you ran, and indicate which font(s) you want to change and the fonts you want to replace them with.

I'm not an expert with templates. I would only know how to do it by redoing the process for each question. Of course, you could always create a macro and just pass the different info to it.

Re: Formatting Crosstabs

RobertNYC — Tue, 21 Feb 2012 05:36:56 GMT

As soon as i get back to my computer, i wil try this out and let you know. Thanks!

Formatting Crosstabs

Cynthia_sas — Tue, 21 Feb 2012 17:52:53 GMT

Hi:

I agree with Art -- the structure of the data is going to make a difference when you are trying to create survey tables. If you need page numbers, then you don't want to go down the HTML road. You should probably stick with PDF or RTF destinations.

I don't have a lot to add. I do have a few observations, though. Almost all my questions from students who are doing surveys are about using PROC TABULATE or PROC REPORT for the survey tables instead of using PROC FREQ. There's a TABULATE example in the doc about creating multi-response survey tables. When your data are structured so that you have 1 obs with Q1, Q2, Q3 individual variables, it leads to needing multiple TABLE statements or multiple procedure steps. But if your data were in a slightly different structure, then TABULATE could do it with BY and/or PAGE processing and REPORT could do it with BY or PAGE processing -- no TABLE template needed. TABULATE and REPORT both have ways to directly provide a format (without a TABLE template) and they both have ways to provide a "header" on top of each table (TABULATE with PAGE dimension, REPORT with COMPUTE BEFORE). Even FREQ could take data in a different structure and produce multiple tables if you just used BY group processing (such as BY QUESTION).

And, for changing fonts, both TABULATE and REPORT have the STYLE= overrides. If you can get what you want out of FREQ and you only have 3 questions, then that's probably an acceptable approach, but TABLE templates and STYLE templates will be an extra learning curve.

But if you have something closer to 50-100 questions, then I'd recommend taking a look at some of this information and the examples.

cynthia

These papers discuss the production of survey "books" or survey tables:
http://support.sas.com/resources/papers/proceedings09/150-2009.pdf
http://analytics.ncsu.edu/sesug/2010/CC02.Go.pdf
http://www.nesug.org/proceedings/nesug05/ap/ap18.pdf
http://www2.sas.com/proceedings/sugi31/172-31.pdf
http://www2.sas.com/proceedings/sugi30/091-30.pdf
http://www2.sas.com/proceedings/sugi31/194-31.pdf
http://www2.sas.com/proceedings/sugi23/Posters/p184.pdf

And, the TABULATE doc even has an example of producing output for multi-response survey data:
http://support.sas.com/documentation/cdl/en/proc/63079/HTML/default/viewer.htm#p1mcul17bwwvfhn19hoojfbxp2oy.htm
and a Tech Support note on restructuring your data: http://support.sas.com/techsup/technote/ts277.html