Help on proc report: display frequency count and percentage within one cell

[guanlinchang]

I am not a professional SAS programmer, but I am working as a statistics intern now to help producing some report.

I have data like this:

data test;
  input ID $  type1 $ type2 $;
  datalines;
  1 A1 B1
  2 A1 B2
  3 A2 B2
  4 A1 B2
  5 A2 B1
  6 A2 B1
  ;
run;

So we have: A1 and B1: 1 subject; A1 and B2: 2 subjects; A2 and B1: 2 subjects; A2 and B2: 1 subject

My goal:

To generate a frequency table using proc report and achieve the following lay out:

I am very new to proc report. I have been getting basic understanding of this procedure from http://support.sas.com and I understand those examples, however none of the examples help me to get idea how to generate this exactly.

Any advice or help would be appreciated.

[Cynthia_sas · Accepted solution]

Hi:

  It is useful to understand what PROC REPORT will give you by default and make sure you get the right numbers and then work toward cosmetically improving the report to look like what you want.

  Here are some points to remember:

1) Almost all SAS procedures will only give you 1 value for cell/column. So, for example, on a report, by default, SAS will usually be able to give you a column for the N or the count and give you another column for the percent of count. But unless you create a character value that is the combination of (N/%), SAS won't put 2 values in one cell.

2) If you want the count in the Header for a variable value, as you show with your N=6 or N=3, then you need to pre-calculate these values and make helper macro variables to hold the counts you need. There are several different ways to do this. I show just one way in the modified data creation program below.

Here's the modified DATA step to read the data, accumulate the totals and make helper macro variables using CALL SYMPUTX:

  Using PROC REPORT, there are 3 different examples of how you might get the default counts and percents, each count and percent in its own cell:

This is what you have to understand first because you need to make sure you can generate the numbers you want correctly, taking the defaults before you start on finessing the cosmetics of the report.

Then, next, you would need to understand the PROC REPORT COMPUTE block if you really need to get the (N,%) in one cell -- because you'll need to make a character column to hold the concatenated values and display the concatenated values.

There are several different postings in the Forums about how to make computed columns in PROC REPORT. You might want to look for those.

Cynthia

[guanlinchang]

Hi, Cynthia:

This is very helpful.

I appreciate you breaking things down for me this way, and it definitely makes a lot of sense.

I guess this is a good start for me. My actual data is not provided with datalines though, it comes with the extraction form a database and is already saved into a SAS format, however I do not think this is a issue for me.

Thank you for your swift and detailed response.

[Cynthia_sas]

Hi,

  If you already have SAS datasets, then you can use other techniques to get your overall counts that you need -- you could use PROC FREQ, PROC MEANS, PROC SQL.

Cynthia