Most frequent observation

[sweetpeaindeed]

I'm trying to figure out how to calculate the most frequent date for a particular observation. Basically, I have multiple rows for one person (one ID). In each of those rows are different death_dates. How do I find the most frequent death_date for all the patients in my data set?

For example:

ID DEATH_DATE
1 24MAR2001
1 24MAR2001
1 25MAR2001
2 01MAY2002
2 01MAY2002
2 01MAY2002
2 29APR2002

My procedure would result in a new variable acknowledging 24MAR2001 for ID 1 and 01MAY2002 for ID 2.

Any help would be greatly appreciated! I am completely stuck!

[DBailey]

not commenting on the metaphysical nature of your question....

If you don't like creating the views expressly, you could it via inline views...just a bit harder to follow. Also not sure what you want to happen if there is a tie.

data work.ddates;
input id death_date:date9.;
format death_date date9.;
cards;
1 24MAR2001
1 24MAR2001
1 25MAR2001
2 01MAY2002
2 01MAY2002
2 01MAY2002
2 29APR2002
;

proc sql;
create view work.vDeadCounts as
select
id,
death_date,
count(*) as Times_Dead
from work.ddates
group by id, death_date
;

create view work.vMaxCounts as
select id, max(Times_Dead) as Max_Times_Dead
from work.vDeadcounts group by id;

select distinct
t1.*
from
work.vDeadCounts t1
inner join work.vMaxCounts t2
on t1.id=t2.id and t1.Times_Dead=t2.Max_Times_Dead;
quit;

[sweetpeaindeed]

Ok, one more question. I need to somehow merge this back to my data set and not just in my output. I tried to use ods output to create an excel file but I can't seem to create that. Is there any other way to make the output it's own data set that I can merge back with my original data set?

Thanks sooo much!

[DBailey]

create table work.tmp as
select distinct
t1.*
from
work.vDeadCounts t1
inner join work.vMaxCounts t2
on t1.id=t2.id and t1.Times_Dead=t2.Max_Times_Dead;
quit;

then you could merge back to your original dataset.

[Howles]

The requested result is known as the MODE. It is available from either PROC UNIVARIATE or PROC MEANS/SUMMARY.

Here is some better test data, raising the issue of ties:

data work.ddates;
input id death_date:date9.;
format death_date date9.;
cards;
1 24MAR2001
1 24MAR2001
1 25MAR2001
2 01MAY2002
2 01MAY2002
2 29APR2002
2 29APR2002
2 11AUG2002
2 11AUG2002
;

Let PROC SUMMARY deliver the modes:

proc summary data=ddates ;
class id ;
types id ;
var death_date ;
output out=modes(drop = _type_ _freq_) mode(death_date)=mode_dd ;
run ;

Then merge with the original data:

data want1 ;
merge ddates modes ;
by id ;
run ;

It's also possible to do it all in a single SQL statement with a lot of nested inline views:

create table want2 as
select *
from (select id , min(death_date) as mode_dd format=date9.
from (select *
from (select * , count(*) as many
from ddates
group by id , death_date
)
group by id
having many EQ max(many)
)
group by id
having death_date EQ min(death_date)
)
natural join
ddates
;

The innermost view counts the occurrences of the dates. The next one finds the modes. Then, ties are eliminated by arbitrarily keeping the smallest value. Finally, the outermost SELECT joins the result with the original data.

[sweetpeaindeed]

Thank you!

In regards to the metaphysical nature of my work -- I work for Veteran Affairs so mortality is a pretty common variable. Depressing, I know. 😞

But thank you again; I greatly appreciate it!

[Ksharp]

[pre]
data work.ddates;
input id death_date:date9.;
format death_date date9.;
cards;
1 24MAR2001
1 24MAR2001
1 25MAR2001
2 01MAY2002
2 01MAY2002
2 01MAY2002
2 29APR2002
;
run;
proc freq data=ddates noprint;
tables id*death_date /list nopercent out=freq;
run;
proc sort data=ddates;
by id death_date;
run;
data want;
merge ddates freq(drop=percent);
by id death_date;
run;
[/pre]

Ksharp

[barheat]

This is a problem that I have had to deal with so many times (most frequent defect) that I built a macro.
Purpose is to identify the most frequent value of a variable for each classification variable. The output is a data table.

For your example the usage would be:
%freq_class(dsnin=data_table_name, var=death_date, class=id, dsnout=death_dates);

%macro freq_class(dsnin=, var=, class=, dsnout=);
/* dsnin = input data set name */
/* var = variable to analyze */
/* class = classification variable */
/* dsnout = output data set name */
proc freq data=&dsnin noprint;
tables &var*&class /norow nocol nocum nopercent out=&dsnout;
run;

proc sort data=&dsnout;
by &class descending count;
run;

data &dsnout;
set &dsnout;
by &class;
if first.&class;
run;
%mend;