Running count distinct by group through time

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 15
Accepted Solution

Running count distinct by group through time

[ Edited ]

Hi, I have below dataset. I need to count distinct values of id2 by id1 through time. The output I want is the runningcount1 column. Additionally, I need to repeat the same procedure for the last 360 days. Checking last 360 days and counting distinct number of id2 by id1. If it goes less than 360 days back, then it should count distinct values of id2 by id1 however many days it goes back. The output I want is in runningcount2 column. Data is sorted by id1 and time. Thank you very much.

 

id1 id2 time   runningcount1 runningcount2
1    A  20131128   1 1
1    B  20140214   2 2
1    C  20140530   3 3
1    C  20140622   3 3
1    D  20140831   4 4
1    D  20141220   4 3
1    A  20150217   4 3
1    A  20150302   4 3
1    C  20150410   4 3
1    D  20150425   4 3
1    E  20150609   5 4
1    F  20151025   6 5
1    C  20160612   6 2
1    D  20180101   6 1
2    A  19900515   1 1
2    B  19900813   2 2
2    E  19910522   3 2
2    A  19910524   3 3
2    F  19910919   4 3
2    G  19920101   5 4
2    A  19930321   5 1


Accepted Solutions
Solution
‎02-21-2018 12:40 PM
PROC Star
Posts: 217

Re: Running count distinct by group through time

You can do it with a hash table, e.g.:

data have;
  input id1 @6 id2 $ @9 time yymmdd8.;
  format time date9.;
cards;
1    A  20131128
1    B  20140214
1    C  20140530
1    C  20140622
1    D  20140831
1    D  20141220
1    A  20150217
1    A  20150302
1    C  20150410
1    D  20150425
1    E  20150609
1    F  20151025
1    C  20160612
1    D  20180101
2    A  19900515
2    B  19900813
2    E  19910522
2    A  19910524
2    F  19910919
2    G  19920101
2    A  19930321
;run;



data want;
  set have;
  by id1;
  if first.id1 then do;
    if _N_=1 then do;
      declare hash h();
      rc=h.definedata('lasttime');
      rc=h.definekey('id2');
      h.definedone();
      declare hiter iter('h');
      end;
    else
      h.clear();
    end;
  rc=h.find();
  lasttime=time;
  if rc then
    h.add();
  else
    h.replace();
  runningcount1=h.num_items;
  rc=iter.first();
  starttime=intnx('year',time,-1,'same');
  runningcount2=0;
  do until(iter.next());
    if lasttime>=starttime then
      runningcount2=runningcount2+1;
    end;
  keep id1 id2 time runningcount1 runningcount2;
run;

View solution in original post


All Replies
Super Contributor
Posts: 498

Re: Running count distinct by group through time

Please post the contents of the excel-file as data-step using datalines statement, so that we don't have to guess how the data looked like when you imported the file.

Solution
‎02-21-2018 12:40 PM
PROC Star
Posts: 217

Re: Running count distinct by group through time

You can do it with a hash table, e.g.:

data have;
  input id1 @6 id2 $ @9 time yymmdd8.;
  format time date9.;
cards;
1    A  20131128
1    B  20140214
1    C  20140530
1    C  20140622
1    D  20140831
1    D  20141220
1    A  20150217
1    A  20150302
1    C  20150410
1    D  20150425
1    E  20150609
1    F  20151025
1    C  20160612
1    D  20180101
2    A  19900515
2    B  19900813
2    E  19910522
2    A  19910524
2    F  19910919
2    G  19920101
2    A  19930321
;run;



data want;
  set have;
  by id1;
  if first.id1 then do;
    if _N_=1 then do;
      declare hash h();
      rc=h.definedata('lasttime');
      rc=h.definekey('id2');
      h.definedone();
      declare hiter iter('h');
      end;
    else
      h.clear();
    end;
  rc=h.find();
  lasttime=time;
  if rc then
    h.add();
  else
    h.replace();
  runningcount1=h.num_items;
  rc=iter.first();
  starttime=intnx('year',time,-1,'same');
  runningcount2=0;
  do until(iter.next());
    if lasttime>=starttime then
      runningcount2=runningcount2+1;
    end;
  keep id1 id2 time runningcount1 runningcount2;
run;
Occasional Contributor
Posts: 15

Re: Running count distinct by group through time

Thank you very much s_lassen. This was very helpful.

Super User
Posts: 10,511

Re: Running count distinct by group through time

data have;
  input id1 @6 id2 $ @9 time yymmdd8.;
  format time date9.;
cards;
1    A  20131128
1    B  20140214
1    C  20140530
1    C  20140622
1    D  20140831
1    D  20141220
1    A  20150217
1    A  20150302
1    C  20150410
1    D  20150425
1    E  20150609
1    F  20151025
1    C  20160612
1    D  20180101
2    A  19900515
2    B  19900813
2    E  19910522
2    A  19910524
2    F  19910919
2    G  19920101
2    A  19930321
;run;
proc sql;
select *,(select count(distinct id2) from have where id1=a.id1 and time le a.time) as count1,
(select count(distinct id2) from have where id1=a.id1 and time between a.time-360 and a.time) as count2
 from have as a;
quit;
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 161 views
  • 4 likes
  • 4 in conversation