topic Re: Count while skipping duplicates in proc freq in SAS Programming

Count while skipping duplicates in proc freq

statadm — Tue, 22 Sep 2009 13:53:04 GMT

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!

Re: Count while skipping duplicates in proc freq

deleted_user — Tue, 22 Sep 2009 14:05:15 GMT

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.

Re: Count while skipping duplicates in proc freq

statadm — Tue, 22 Sep 2009 14:07:34 GMT

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

Re: Count while skipping duplicates in proc freq

Flip — Tue, 22 Sep 2009 15:06:08 GMT

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

Re: Count while skipping duplicates in proc freq

deleted_user — Tue, 22 Sep 2009 15:06:43 GMT

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.

Re: Count while skipping duplicates in proc freq

statadm — Tue, 22 Sep 2009 15:17:29 GMT

Thanks Flip, it works great.

Sorry about the misunderstanding concerning cleaning the data.

Re: Count while skipping duplicates in proc freq

data_null__ — Tue, 22 Sep 2009 16:08:19 GMT

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]