topic Re: Enumerate unique value by group in SAS Programming

Enumerate unique value by group

inyli — Wed, 08 Jan 2020 00:24:24 GMT

Hi, I'm having a question regarding how to enumerate unique date by group - id, see below:

id. date rank

123. 20190101. 1

123. 20190102. 2

123 20190102. 2

how do I get the rank column?

Re: Enumerate unique value by group

novinosrin — Wed, 08 Jan 2020 00:38:54 GMT

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

Re: Enumerate unique value by group

novinosrin — Wed, 08 Jan 2020 01:18:49 GMT

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;

Re: Enumerate unique value by group

ed_sas_member — Wed, 08 Jan 2020 15:26:54 GMT

Hi @inyli

data want;
	set have;
	by id date;
	if first.id then flag = 0;
	if first.date then flag + 1;
run;

Re: Enumerate unique value by group

inyli — Thu, 09 Jan 2020 13:23:48 GMT

Thanks!!!!

Re: Enumerate unique value by group

inyli — Thu, 09 Jan 2020 13:24:14 GMT

Thank you! It works:)