conditional count in proc sql

Accepted Solution Solved
Reply
Regular Contributor
Posts: 161
Accepted Solution

conditional count in proc sql

Dear All:

Could you please tell me how to count a variable only if certain criteria is met in the proc sql syntax.  For example, I want to count both people and Hispanic people.  So I coded the following way, but the calculated p_count and chm_count is the same.  Also, if I want to count rows in which certain variable called sex is F, could I go for count(sex = "F")?

My old but stupid way is to use proc sql two time with the where statement and then join these two tables.  That is not cool.

Thanks!

proc sql;

     create table ipums2 as

           select distinct year, statefip, county, count(pid) as p_count, count(race=4) as hs_count

                from ipums

                     group by year, statefip, county

                           order by year, statefip, county;

quit;


Accepted Solutions
Solution
‎02-10-2014 09:44 AM
Super User
Super User
Posts: 6,502

Re: conditional count in proc sql

Since SAS evaluates boolean expressions to 1/0 you can just change the second COUNT() to SUM().

View solution in original post


All Replies
Super User
Posts: 5,260

Re: conditional count in proc sql

I don't know what your final result should be (in what form). In many cases, just adding your column to the group by clause is sufficient. If you want specific grouping, you can hold these in control tables (and apply them using SAS formats or SQL join). Adding specific criteria as a basic technique can lead to hard coded programs which lead higher maintenance costs.

Having that said, you could combine case with aggregation functions:

sum(case race when 4 then 1 else 0 end) as hs_count

Data never sleeps
Solution
‎02-10-2014 09:44 AM
Super User
Super User
Posts: 6,502

Re: conditional count in proc sql

Since SAS evaluates boolean expressions to 1/0 you can just change the second COUNT() to SUM().

☑ This topic is SOLVED.

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

Discussion stats
  • 2 replies
  • 15619 views
  • 5 likes
  • 3 in conversation