Hi, I'm having a question regarding how to enumerate unique date by group - id, see below:
id. date rank
123. 20190101. 1
123. 20190101. 1
123. 20190102. 2
123 20190102. 2
how do I get the rank column?
data have;
input id date :yymmdd8.;
format date yymmdd10.;
cards;
123 20190101 1
123 20190101 1
123 20190102 2
123 20190102 2
;
proc rank data=have out=ranked ties=dense ;
by id;
var date;
ranks ranked_date;
run;
123 2019-01-01 1
123 2019-01-01 1
123 2019-01-02 2
123 2019-01-02 2
data have;
input id date :yymmdd8.;
format date yymmdd10.;
cards;
123 20190101 1
123 20190101 1
123 20190102 2
123 20190102 2
;
proc rank data=have out=ranked ties=dense ;
by id;
var date;
ranks ranked_date;
run;
123 2019-01-01 1
123 2019-01-01 1
123 2019-01-02 2
123 2019-01-02 2
Alternatively, since it is sweetly sorted, you could have some fun like
data have;
input id date :yymmdd8.;
format date yymmdd10.;
cards;
123 20190101 1
123 20190101 1
123 20190102 2
123 20190102 2
;
data want_ranked;
do _n_=1 by 1 until(last.id);
do until(last.date);
set have;
by id date;
ranked_date=_n_;
output;
end;
end;
run;
/*Or*/
data want_ranked;
set have;
by id date;
if first.id then ranked_date=1;
else if first.date then ranked_date+1;
run;
proc sql;
create table want_ranked as
select a.* ,count(distinct b.date) as ranked_date
from have a inner join (select distinct id,date from have) b
on a.id=b.id and b.date<=a.date
group by a.id,a.date
having max(b.date)=b.date
order by a.id,a.date;
quit;
Hi @inyli
data want;
set have;
by id date;
if first.id then flag = 0;
if first.date then flag + 1;
run;
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
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.
Ready to level-up your skills? Choose your own adventure.