topic Re: Running count distinct by group through time in SAS Programming

Running count distinct by group through time

lezgin — Wed, 21 Feb 2018 02:25:03 GMT

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

Re: Running count distinct by group through time

andreas_lds — Wed, 21 Feb 2018 08:04:53 GMT

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.

Re: Running count distinct by group through time

s_lassen — Wed, 21 Feb 2018 15:12:15 GMT

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;

Re: Running count distinct by group through time

lezgin — Wed, 21 Feb 2018 17:40:47 GMT

Thank you very much s_lassen. This was very helpful.

Re: Running count distinct by group through time

Ksharp — Thu, 22 Feb 2018 06:05:12 GMT

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;