Desktop productivity for business analysts and programmers

Weighted Frequency Table in EG producing slightly different results than weighted Proc Freq in Base SAS

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 10
Accepted Solution

Weighted Frequency Table in EG producing slightly different results than weighted Proc Freq in Base SAS

I am a Base SAS user that has been learning Enterprise Guide (6.1) over the last couple months.  As I've been converting some of my Base SAS programs over to EG as a learning exercise, I've come across a problem - I get slightly different results with a weighted frequency depending on which program I use.  My data is a survey sample of 3,508 observations that includes a weight variable to extrapolate the information to approximately 535,000 cases.

In Base SAS, I'm running a simple Proc Freq table on a single variable and weighting it with the "WEIGHT" statement.  I get a total of 316,666 observations for the conditions set.

When I run a Summary Tables task on the SAME dataset, and assign the weight variable to the "Frequency Count" task role, I get a total of 315,461 observations for the same conditions.

Am I missing something in how to weight data properly in Enterprise Guide?


Accepted Solutions
Solution
‎02-04-2015 11:44 AM
Community Manager
Posts: 2,693

Re: Weighted Frequency Table in EG producing slightly different results than weighted Proc Freq in Base SAS

Summary Tables is using PROC TABULATE behind the scenes.  You might see if there are nuances in how TABULATE and FREQ use WEIGHT, or treat MISSING differently.  For a better comparison, you might try the Table Analysis task instead -- it uses PROC FREQ for n-way frequency analysis.

Chris

View solution in original post


All Replies
Grand Advisor
Posts: 10,210

Re: Weighted Frequency Table in EG producing slightly different results than weighted Proc Freq in Base SAS

Frequency count is likely assuming that the values are that, counts. Which would be integers. So likely the fractional part of your weights is getting discounted. As and exercise, examine the integer and fractional parts of your weight variable and sum them separately. See if you get values close to 315,461 and 1205.

Solution
‎02-04-2015 11:44 AM
Community Manager
Posts: 2,693

Re: Weighted Frequency Table in EG producing slightly different results than weighted Proc Freq in Base SAS

Summary Tables is using PROC TABULATE behind the scenes.  You might see if there are nuances in how TABULATE and FREQ use WEIGHT, or treat MISSING differently.  For a better comparison, you might try the Table Analysis task instead -- it uses PROC FREQ for n-way frequency analysis.

Chris

Trusted Advisor
Posts: 1,052

Re: Weighted Frequency Table in EG producing slightly different results than weighted Proc Freq in Base SAS

I've hit this before. I can't remember the exact details, but in one of the SAS procedures using a particular option truncates the weight to an integer, while there is a different option to use the full value of the weight. Some experimentation should clear it up for you.

Tom

Occasional Contributor
Posts: 10

Re: Weighted Frequency Table in EG producing slightly different results than weighted Proc Freq in Base SAS

Thanks for the help!  Looks like PROC TABULATE's "FREQ" statement does indeed truncate the weights; when I choose the One-Way Frequency task instead of Summary Tables task, I get the correct values.  I guess I'll just have to be very careful which task I use when creating frequencies for my sample data.

Trusted Advisor
Posts: 1,052

Re: Weighted Frequency Table in EG producing slightly different results than weighted Proc Freq in Base SAS

You can use Summary Tables, just make sure you use the Relative Weight instead of the Frequency Count. There are a few things that Summary Tables does that can't be done easily any other way.

Occasional Contributor
Posts: 10

Re: Weighted Frequency Table in EG producing slightly different results than weighted Proc Freq in Base SAS

I didn't find that assigning the weight to Relative Weight worked in Summary Tables - my output still only included the actual number of observations when I did that and not the weighted values.  My understanding is that Relative Weight only works when computing statistics on other variables, not on frequency counts.

Trusted Advisor
Posts: 1,052

Re: Weighted Frequency Table in EG producing slightly different results than weighted Proc Freq in Base SAS

Sorry, I was only looking at this thread with half an eye, and didn't notice that the discussion was about frequency counts. To get a weighted frequency count out of Summary Tables, use the Relative Weight, and the statistic that you want is Sum of Weights instead of N. I just tested it, and it works fine. Let me know if you have any problems.

Occasional Contributor
Posts: 10

Re: Weighted Frequency Table in EG producing slightly different results than weighted Proc Freq in Base SAS

I tried to do this myself and it won't let me use the SumWgt statistic - I get a crossed out symbol when I try to drop it on the table.  The other stats work fine, and I have my weight variable set to the Relative Weight role. What am I missing?

Trusted Advisor
Posts: 1,052

Re: Weighted Frequency Table in EG producing slightly different results than weighted Proc Freq in Base SAS

Although you're conceptually only doing a frequency count, I think that EG wants you to apply the Sum of Weights on an analysis variable. Try putting any numeric variable into your table request (make sure there are no missing values), and try applying the sum of weights statistic on that.

One thing to be aware of with PROC FREQ is it will fail most quickly because of a large result set...PROC TABULATE is better, and PROC MEANS is basically impossible to break.

Occasional Contributor
Posts: 10

Re: Weighted Frequency Table in EG producing slightly different results than weighted Proc Freq in Base SAS

Well, I won't be able to use Summary Tables then, because most of the variables I am evaluating are character variables. Thanks for the help!

Trusted Advisor
Posts: 1,052

Re: Weighted Frequency Table in EG producing slightly different results than weighted Proc Freq in Base SAS

Just add a numeric variable named "DummyVar" with a value of 1 on every record to your table, and you're good to go!

Tom

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 11 replies
  • 852 views
  • 7 likes
  • 4 in conversation