BookmarkSubscribeRSS Feed
DrNO811
Calcite | Level 5

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?

3 REPLIES 3
Fugue
Quartz | Level 8

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
Onyx | Level 15

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

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 5496 views
  • 0 likes
  • 4 in conversation