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

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

SWEETSAS — Sat, 21 Oct 2017 16:36:36 GMT

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.

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

Shmuel — Sat, 21 Oct 2017 17:09:18 GMT

Pay attention:

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

results into zero observations.

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

PGStats — Sat, 21 Oct 2017 18:38:15 GMT

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;

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

SWEETSAS — Sat, 21 Oct 2017 18:43:38 GMT

Many thanks! It worked.