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?
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?
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:
input id death_date:date9.;
format death_date date9.;
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 ;
Then merge with the original data:
data want1 ;
merge ddates modes ;
by id ;
It's also possible to do it all in a single SQL statement with a lot of nested inline views:
create table want2 as
from (select id , min(death_date) as mode_dd format=date9.
from (select *
from (select * , count(*) as many
group by id , death_date
group by id
having many EQ max(many)
group by id
having death_date EQ min(death_date)
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.
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;
proc sort data=&dsnout;
by &class descending count;