Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Home
- /
- Programming
- /
- SAS Procedures
- /
- Proc Tabulate doesn't match Proc Freq

Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

🔒 This topic is **solved** and **locked**.
Need further help from the community? Please
sign in and ask a **new** question.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Posted 06-26-2018 10:40 AM
(1316 views)

Hi,

I ran a proc tabulate procedure, but in the ouputs, there are some "missing" cells. However, a small percent should appear in these cells. When I do the same table with proc freq, the expected value is there.

Here is the codes with proc tabulate and the outputs are attached.

The link for the dataset: https://www.dropbox.com/s/wn8vp7l2ioj3pkp/migration_edu_calib.sas7bdat?dl=0

```
proc tabulate data=work.migration_edu_calib out=work.base_age_prop format=20.19;
class v2 v3;
table v2, v3*ROWPCTN;
freq pond_edu;
run;
```

proc freq data=work.migration_edu_calib;

table v2*v3/nopercent nocol nofreq;

weight pond_edu;

run;

See for instance v2=23 * v3=22, or the whole col v3=97. I guess it was something to do with the weight/freq statement. I use the proc tabulate because I want a output in a SAS dataset where percents are in a single column.

1 ACCEPTED SOLUTION

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

4 REPLIES 4

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Hi:

Sorry, I don't like to open Excel files. I am curious why your FREQ has NOPERCENT but your TABULATE has ROWPCTN.

I'm also wondering what your ODS destination of choice is (RTF, PDF, HTML, EXCEL, etc) and why you are creating an OUT= dataset from PROC TABULATE.

By default, PROC FREQ only gives you 2 decimal places for the percents that it calculates, while PROC TABULATE gives you a formatting choice. The issue you're running into could be as simple as a rounding issue or could have to do with WEIGHT. Most of the time, when I try the same type of code on a SASHELP dataset, but without weights, rounding accounts for all the differences in numbers. Note how with SASHELP.SHOES and no WEIGHT or FREQ involved, I get the same numbers for ROWPCTN -- for example 16.666666666 is rounded to 16.67 because FREQ always displays percents as 2 decimal places and 83.3333333 is displayed as 83.33:

You might want to work with Tech Support if you can't figure out how to replicate your FREQ results with TABULATE or whether it is possible.

Cynthia

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

If your Weight / freq variable is not an integer I would not be surprised to see slightly different results as I the two procedures may use slightly different algorithms as to when the decimal portions are truncated.

And without raw data it is hard to diagnose from "output", especially in Excel, as the two procedures create output slightly differently. Note that in your outputs document that the values in the "tabulate" are being rounded by Excel functions while the "freq" output only has 2 significant digits.

Any time a percentage is involved if the numerator and denominator cannot be supplied it is a bit of guessing game as to where an actual difference comes from.

Rerun your code including N in the Proc tabulate and remove the Nofreq from proc freq output. You might consider adding some ALL bits in the Tabulate table to get the column and row total N as well.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

@Demographer wrote:

I still don't get why the WEIGHT statement doesn't work properly with proc tabulate.

It depends on what you mean by "work properly". And since you were using a FREQ statement not Weight I'm a tad confused.

Weight and Freq in the procedures that support both behave differently but as intended. The main obvious difference is the N used in things like STD.

See this example using the same variable for WEIGHT and FREQ. The "weight" variable is not an actual statistical weight but the values demonstrate the differences in behavior of between the two options. Note that the Means are very similar but the N and STD are quite different.

proc tabulate data=sashelp.class; title "Tabulate with weight"; class sex; var height; table sex, height*(n mean std ); weight weight; run; proc tabulate data=sashelp.class; title "Tabulate with freq"; class sex; var height; table sex, height*(n mean std ); freq weight; run;

And anywhere FREQ is used in this manner the value is expected to be a count, sort of positive integers only have effect. Also Proc freq applies the decimal portions of the weight: See below example. Notice that the PROC freq weight creates a fractional person for males (sex=M) in the frequency column. Proc Means uses the same behavior for weight and freq as Proc tabulate does.

proc freq data=sashelp.class; weight weight; tables sex; run; proc means data=sashelp.class n mean; title "Proc means with weight"; class sex; var age; weight weight; run; title; proc means data=sashelp.class n mean; title "Proc means with freq"; class sex; var age; freq weight; run; title;

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

**If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. **

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.