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.
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;
Pay attention:
... from have where a=10 ...
results into zero observations.
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;
Many thanks! It worked.
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.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.