Proc Tabulate doesn't match Proc Freq

[Demographer]

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.

[Cynthia_sas]

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

[ballardw]

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.

[Demographer · Accepted solution]

Problem solved. I had some weight <1, so these cases were count as 0 with the FREQ statement of the proc tabulate. I just multiply my weight by 1000 and now all work. I still don't get why the WEIGHT statement doesn't work properly with proc tabulate.

[ballardw]

---

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