topic SAS Enterprise Guide 4.3 Query Builder: Simple Count in SAS Enterprise Guide

SAS Enterprise Guide 4.3 Query Builder: Simple Count

DrNO811 — Fri, 07 Feb 2014 00:46:33 GMT

I was attempting to use the query builder to get a two column summary table that showed the first column as the Age Group for my observations, and the second column being the number of times each age group shows up in the data. Here was the code the query builder created:

PROC SQL;

CREATE TABLE WORK.QUERY_FOR_CUMULATIVE_W_AGEGROUP AS

SELECT t1.AgeGroup, (Count(t1.AgeGroup)) AS COUNT_of_AgeGroup

FROM WORK.CUMULATIVE_W_AGEGROUP t1

GROUP BY t1.AgeGroup;

QUIT;

But when I run this code, the counts aren't correct. I can't figure out what it is trying to count, but they add up to 1,000. I have over 2 million rows, and I checked the table it calls, and every row has an entry, so I'm not sure why the count is off. I was able to get at the information using the summary table tools, but this technique is going to be important for me to use in other calculations. Any thoughts on what's causing it not to work as expected?

Re: SAS Enterprise Guide 4.3 Query Builder: Simple Count

Fugue — Fri, 07 Feb 2014 02:31:54 GMT

Check the name of the input table . . . .perhaps you are accidentally querying an already pre-summarized or filtered table?

Create a program node, make sure you have it set to run on the right server, and copy/paste and run (F8) the following code:

proc sql;

create table agg_count as

select count ( * ) as obs

from cumulative_W_agegroup

;

quit;

If the counts are the same as the query you posted, then I suspect you are referencing the wrong table.

Re: SAS Enterprise Guide 4.3 Query Builder: Simple Count

TomKari — Fri, 07 Feb 2014 14:38:18 GMT

I'm afraid I don't have anything to add beyond what Fugue has said. It looks like the code should work, I don't see any reason why your results are wrong.

As Fugue says, make sure it's being run on the correct server, that's all I can think of.

Tom

Re: SAS Enterprise Guide 4.3 Query Builder: Simple Count

anonymous_user — Mon, 10 Feb 2014 12:06:10 GMT

Hi,

You could try a different way to count your variable by creating a "listing" variable that is equal to 1, and then summing it up by the specific group.

1. Create a new variable using the "advanced expression" and enter: 1

2. Name it as "listing".

3. Pick the "sum" function from the summery options.

It will count the correct amount of OBS like a charm

Good luck