topic Re: Most frequent observation in SAS Procedures

Most frequent observation

sweetpeaindeed — Mon, 09 May 2011 21:17:45 GMT

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!

Re: Most frequent observation

DBailey — Mon, 09 May 2011 21:50:58 GMT

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;

Re: Most frequent observation

sweetpeaindeed — Mon, 09 May 2011 22:06:57 GMT

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!

Re: Most frequent observation

sweetpeaindeed — Mon, 09 May 2011 22:42:35 GMT

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!

Re: Most frequent observation

DBailey — Mon, 09 May 2011 22:53:20 GMT

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.

Re: Most frequent observation

Ksharp — Tue, 10 May 2011 03:36:03 GMT

[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

Re: Most frequent observation

Howles — Wed, 11 May 2011 22:02:15 GMT

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.

Re: Most frequent observation

barheat — Thu, 12 May 2011 08:17:28 GMT

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;