Help using Base SAS procedures

Creating a data set with zero count by group variable in SQL

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 78
Accepted Solution

Creating a data set with zero count by group variable in SQL

I am trying to create a data set using SQL  in which the count is grouped by another variable. It happens that no record meet the criteria, so I expect it to bring back zero by the group variable. For some reasons, I keep on getting zero rows.

 

For example, suppose my data is like this:

data have;
input id a b trtpn;
datalines ;
1 2 4 1
2 3 5 1
3 4 0 2
4 5 1 2
4 2 1 3
3 3 4 3
3 5 7 3
;
run;

proc sql noprint;
create table want as
select trtpn,count(distinct id) as count
from have
where a=10
group by trtpn;

quit;

 

If it's correct ,the output should look like the following:


trtpn count
1     0
2     0

3     0

 Any help is appreciated.


Accepted Solutions
Solution
‎10-21-2017 02:42 PM
Esteemed Advisor
Posts: 5,535

Re: Creating a data set with zero count by group variable in SQL

Get what you want with:

 

proc sql;
create table want as
select 
    trtpn,
    count(distinct case when a=10 then id else . end) as count
from have
group by trtpn;
select * from want;
quit;
PG

View solution in original post


All Replies
Trusted Advisor
Posts: 1,837

Re: Creating a data set with zero count by group variable in SQL

Pay attention:

     ... from have where a=10 ...

results into zero observations.

Solution
‎10-21-2017 02:42 PM
Esteemed Advisor
Posts: 5,535

Re: Creating a data set with zero count by group variable in SQL

Get what you want with:

 

proc sql;
create table want as
select 
    trtpn,
    count(distinct case when a=10 then id else . end) as count
from have
group by trtpn;
select * from want;
quit;
PG
Frequent Contributor
Posts: 78

Re: Creating a data set with zero count by group variable in SQL

Many thanks! It worked.

☑ This topic is solved.

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

Discussion stats
  • 3 replies
  • 363 views
  • 1 like
  • 3 in conversation