BookmarkSubscribeRSS Feed
sweetpeaindeed
Calcite | Level 5
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!
7 REPLIES 7
DBailey
Lapis Lazuli | Level 10
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
Calcite | Level 5
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
Lapis Lazuli | Level 10
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
Quartz | Level 8
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
Calcite | Level 5
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
Super User
[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
Fluorite | Level 6
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;

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 7 replies
  • 9065 views
  • 3 likes
  • 5 in conversation