SAS Enterprise Guide 4.3 Query Builder: Simple Count

[DrNO811]

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?

[Fugue]

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.

[TomKari]

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

[This widget could not be displayed.]

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