PROC SQL

[mar0000]

How would I select distinct persons per year, but only count each person once total.

An example of my data is:

ID Date
1 20NOV2018
2 06JUN2017
2 29JUL2011
3 05MAY2014
4 04APR2002
4 25APR2009

I want my output to look like:

Year Cnt
2002 1
2009 0
2011 1
2014 1
2017 0
2018 1

[JeffMeyers · Accepted solution]

I think you're only trying to count the person in their earliest date if I understand correctly.  I believe it would be something like this:

data blah;
format date date9.;
input ID 1. @3 Date date9.;
datalines;
1 20NOV2018
2 06JUN2017
2 29JUL2011
3 05MAY2014
4 04APR2002
4 25APR2009
;
run;

proc sql;
    select year(date) as year,count(distinct ifn(date=min_date,id,.)) as num
        from (select *,min(date) as min_date from blah group by id) group by year;
quit;

Which gives me this:

I use the IFN function (IFC if ID is a character variable) to limit the IDs to the first occuring date so the patient isn't double counted (COUNT function doesn't add a missing value).  I use an inline view to calculate the minimum date for each patient since you can't nest the MIN function and the COUNT function in the same query.