BookmarkSubscribeRSS Feed
statadm
Fluorite | Level 6
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!
6 REPLIES 6
deleted_user
Not applicable
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.
statadm
Fluorite | Level 6
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
Flip
Fluorite | Level 6
proc sql;
create table xxxx as select ID, month, count(distinct date) as cnt from yyyy
group by id, month;
quit;
statadm
Fluorite | Level 6
Thanks Flip, it works great.

Sorry about the misunderstanding concerning cleaning the data.
deleted_user
Not applicable
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.
data_null__
Jade | Level 19
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]

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 1015 views
  • 0 likes
  • 4 in conversation