DATA Step, Macro, Functions and more

Count while skipping duplicates in proc freq

Reply
Frequent Contributor
Posts: 78

Count while skipping duplicates in proc freq

Is there a way to do a count by ID of unique dates while ignoring duplicate dates using proc freq?

I just want to count each date one time although it can be used more than once. I need a total count of dates by ID, skipping over the duplicate dates.

It sounds simple enough, but I cannot seem to get it right. I need to get these totals on a bi-monthly basis.

proc freq data=test noprint;
table date /out=counts ;
by id;
where month=8;
title 'August Days';
run;

This does the count for each date, but I now need to total the number of dates counting each date once.

Thanks for any suggestions!
N/A
Posts: 0

Re: Count while skipping duplicates in proc freq

I'm not sure I understand your data. If you succeed, won't your COUNTS all be 1?

In any event, you might want to "clean" the data file first by using SORT with the NODUPKEY option.
Frequent Contributor
Posts: 78

Re: Count while skipping duplicates in proc freq

Well, the duplicates are ok in the dataset so it doesnt' need to be cleaned. I just want a count of the total number of unique dates by ID, not a count of the number of times a date exists for each ID.

I want a count for each month, see sample data:

ID DATE
1 08/01/2009
1 08/03/2009
1 08/03/2009
1 08/05/2009
1 08/08/2009
1 08/08/2009
1 08/08/2009
2 08/02/2009
2 08/05/2009
2 08/05/2009
2 08/09/2009
3 08/10/2009
3 08/11/2009
3 08/12/2009
3 08/12/2009

The total number of dates for ID1 = 4, ID2 =3, ID3 = 3 Message was edited by: statadm
Super Contributor
Posts: 359

Re: Count while skipping duplicates in proc freq

proc sql;
create table xxxx as select ID, month, count(distinct date) as cnt from yyyy
group by id, month;
quit;
Frequent Contributor
Posts: 78

Re: Count while skipping duplicates in proc freq

Thanks Flip, it works great.

Sorry about the misunderstanding concerning cleaning the data.
N/A
Posts: 0

Re: Count while skipping duplicates in proc freq

I meant "cleaned" as a possible approach to the problem. If you elminated the "duplicates" and saved the result in a temp file, then the temp file could be usd for counting purposes.
Respected Advisor
Posts: 3,777

Re: Count while skipping duplicates in proc freq

PROC FREQ will give you those count using the LEVELS proc statement option.

[pre]
data test;
input ID DATE:mmddyy.;
cards;
1 08/01/2009
1 08/03/2009
1 08/03/2009
1 08/05/2009
1 08/08/2009
1 08/08/2009
1 08/08/2009
2 08/02/2009
2 08/05/2009
2 08/05/2009
2 08/09/2009
3 08/10/2009
3 08/11/2009
3 08/12/2009
3 08/12/2009
ods listing close;
proc freq data=test levels;
table date /out=counts noprint;
by id;
*where month=8;
title 'August Days';
ods output nlevels=nlevels;
run;
ods listing;
proc print data=nlevels;
run;
[/pre]
Ask a Question
Discussion stats
  • 6 replies
  • 208 views
  • 0 likes
  • 4 in conversation