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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.