topic Re: Count records based on past dates in SAS Programming

Count records based on past dates

marianob — Thu, 04 Jun 2020 19:09:17 GMT

Hello guys!

I'm struggling to achieve this, but I can't find the solution.

What I need to do, is to count the records where the inquiry_date is older than the per_obs, for each per_obs, and make it accumulative for each id.
So, it must restart when the ID changes. (the per_obs is set at the beginning of the month):

This is the original table:

ID	INQUIRY_DATE	PERIOD
A	9/12/2019	2019-12
A	9/12/2019	2019-12
A	9/12/2019	2019-12
A	4/11/2019	2019-11
A	19/10/2019	2019-10
A	19/10/2019	2019-10
A	8/10/2019	2019-10
A	20/9/2019	2019-09
A	21/8/2019	2019-08
A	21/8/2019	2019-08
A	28/7/2019	2019-07
A	26/7/2019	2019-07
B	23/8/2018	2018-08
B	23/8/2018	2018-08
B	9/8/2018	2018-08
B	13/7/2018	2018-07
B	25/6/2018	2018-06
B	11/5/2018	2018-05
B	18/4/2018	2018-04

This is what I need:

ID	INQUIRY_DATE	PERIOD	Accumulative Inquiry dates
A	9/12/2019	2019-12	9
A	9/12/2019	2019-12	9
A	9/12/2019	2019-12	9
A	4/11/2019	2019-11	8
A	19/10/2019	2019-10	5
A	19/10/2019	2019-10	5
A	8/10/2019	2019-10	5
A	20/9/2019	2019-09	4
A	21/8/2019	2019-08	2
A	21/8/2019	2019-08	2
A	28/7/2019	2019-07	0
A	26/7/2019	2019-07	0
B	23/8/2018	2018-08	3
B	23/8/2018	2018-08	3
B	9/8/2018	2018-08	3
B	13/7/2018	2018-07	3
B	25/6/2018	2018-06	2
B	11/5/2018	2018-05	1
B	18/4/2018	2018-04	0

Any help would be awesome, thanks a lot!

Re: Count records based on past dates

yabwon — Thu, 04 Jun 2020 19:17:44 GMT

What's `per_obs`?

Bart

Re: Count records based on past dates

PaigeMiller — Thu, 04 Jun 2020 19:20:52 GMT

Please explain further the calculations desired.

Why is this one a zero

28/7/2019

2019-07

and this one a two?

21/8/2019

2019-08

Re: Count records based on past dates

marianob — Thu, 04 Jun 2020 19:30:58 GMT

Hi Bart!

Sorry, PER_OB is the same as PERIOD.

Re: Count records based on past dates

novinosrin — Thu, 04 Jun 2020 19:36:25 GMT

Hi @marianob Please try


data have;
input ID $	INQUIRY_DATE :ddmmyy10.	_PERIOD :$7.;
period=input(catx('-',_period,'01'),yymmdd10.);
format period yymmdd10. INQUIRY_DATE ddmmyy10.;
cards;
A	9/12/2019	2019-12
A	9/12/2019	2019-12
A	9/12/2019	2019-12
A	4/11/2019	2019-11
A	19/10/2019	2019-10
A	19/10/2019	2019-10
A	8/10/2019	2019-10
A	20/9/2019	2019-09
A	21/8/2019	2019-08
A	21/8/2019	2019-08
A	28/7/2019	2019-07
A	26/7/2019	2019-07
B	23/8/2018	2018-08
B	23/8/2018	2018-08
B	9/8/2018	2018-08
B	13/7/2018	2018-07
B	25/6/2018	2018-06
B	11/5/2018	2018-05
B	18/4/2018	2018-04
;


data want ;
 if _n_=1 then do;
  dcl hash H (multidata:'y') ;
  h.definekey  ("_INQUIRY_DATE") ;
  h.definedone () ;
  dcl hiter hi('h');
 end;
 do until(last.id);
  set have;
  by id;
  _INQUIRY_DATE=INQUIRY_DATE;
  h.add();
 end;
 do until(last.id);
  set have;
  by id INQUIRY_DATE period notsorted;
  if first.period then do;
   count=0;
   do while(hi.next()=0);
    count=sum(_INQUIRY_DATE<period,count);
   end;
  end;
  output;
 end;
 h.clear();
run;

ID	INQUIRY_DATE	_PERIOD	period	_INQUIRY_DATE	count
A	09/12/2019	2019-12	2019-12-01	21841	9
A	09/12/2019	2019-12	2019-12-01	21841	9
A	09/12/2019	2019-12	2019-12-01	21841	9
A	04/11/2019	2019-11	2019-11-01	21841	8
A	19/10/2019	2019-10	2019-10-01	21841	5
A	19/10/2019	2019-10	2019-10-01	21841	5
A	08/10/2019	2019-10	2019-10-01	21841	5
A	20/09/2019	2019-09	2019-09-01	21841	4
A	21/08/2019	2019-08	2019-08-01	21841	2
A	21/08/2019	2019-08	2019-08-01	21841	2
A	28/07/2019	2019-07	2019-07-01	21841	0
A	26/07/2019	2019-07	2019-07-01	21841	0
B	23/08/2018	2018-08	2018-08-01	21292	4
B	23/08/2018	2018-08	2018-08-01	21292	4
B	09/08/2018	2018-08	2018-08-01	21292	4
B	13/07/2018	2018-07	2018-07-01	21292	3
B	25/06/2018	2018-06	2018-06-01	21292	2
B	11/05/2018	2018-05	2018-05-01	21292	1
B	18/04/2018	2018-04	2018-04-01	21292	0

Re: Count records based on past dates

marianob — Thu, 04 Jun 2020 19:37:51 GMT

Paige,

The first one is 0 because the ID A doesn't have any inquiry_date older than the PERIOD/PER_OBS. (It has two on the same month, 28/7/2019 and 26/7/2019 , but I don't have to consider inquiry_dates on the same period).

Meanwhile, the 2019-08 has two inquiry_dates from the historical data, 28/7/2019 and 26/7/2019, so the counter is marking 2.

Thanks!

Re: Count records based on past dates

yabwon — Thu, 04 Jun 2020 19:40:55 GMT

Double DoW-loop and Hash Table in one, I love it! 😎

Bart

Re: Count records based on past dates

smantha — Thu, 04 Jun 2020 19:44:10 GMT

data new;
informat date ddmmyy10.;
input id $ date period $;
format date mmddyy10.;
cards;;;
A 9/12/2019 2019-12
A 9/12/2019 2019-12
A 9/12/2019 2019-12
A 4/11/2019 2019-11
A 19/10/2019 2019-10
A 19/10/2019 2019-10
A 8/10/2019 2019-10
A 20/9/2019 2019-09
A 21/8/2019 2019-08
A 21/8/2019 2019-08
A 28/7/2019 2019-07
A 26/7/2019 2019-07
B 23/8/2018 2018-08
B 23/8/2018 2018-08
B 9/8/2018 2018-08
B 13/7/2018 2018-07
B 25/6/2018 2018-06
B 11/5/2018 2018-05
B 18/4/2018 2018-04
;;;
run;
proc sort data=new;
by id period;
run;

data new2;
set new;
by id period;
retain cur_queries 0 tot_prev_queries 0;
if first.period then do;

tot_prev_queries = tot_prev_queries+cur_queries;
cur_queries = 0;
end;
cur_queries = cur_queries +1;
if first.id then tot_prev_queries =0;
drop cur_queris;
run;

proc sort data=new2;
by id descending period ;
run;

data new;
informat date ddmmyy10.;
input id $ date period $;
format date mmddyy10.;
cards;;;
A	9/12/2019	2019-12
A	9/12/2019	2019-12
A	9/12/2019	2019-12
A	4/11/2019	2019-11
A	19/10/2019	2019-10
A	19/10/2019	2019-10
A	8/10/2019	2019-10
A	20/9/2019	2019-09
A	21/8/2019	2019-08
A	21/8/2019	2019-08
A	28/7/2019	2019-07
A	26/7/2019	2019-07
B	23/8/2018	2018-08
B	23/8/2018	2018-08
B	9/8/2018	2018-08
B	13/7/2018	2018-07
B	25/6/2018	2018-06
B	11/5/2018	2018-05
B	18/4/2018	2018-04
;;;
run;
proc sort data=new;
by id period;
run;

data new2;
set new;
by id period;
retain cur_queries 0 tot_prev_queries 0;
if first.period then  do;
   
   tot_prev_queries = tot_prev_queries+cur_queries;
   cur_queries = 0;
end;
cur_queries = cur_queries +1;
if first.id then tot_prev_queries =0;
drop cur_queris;
run;

proc sort data=new2;
by id descending period ;
run;

Re: Count records based on past dates

novinosrin — Thu, 04 Jun 2020 19:46:35 GMT

Again, my initial kudos goes to Krakow and Marcin 🙂 ( to whom and Poland I owe my start 🙂 )Btw @yabwon My colleagues love your packages albeit they are complaining that it's not accesible in work professional machines. Can you make it secure for us to access and download if you don't mind. I was gonna reach out to you offline for this request

Re: Count records based on past dates

yabwon — Thu, 04 Jun 2020 19:51:04 GMT

Just define "secure for us to access and download" and I gladly help.
Bart

Re: Count records based on past dates

novinosrin — Thu, 04 Jun 2020 19:53:11 GMT

Alright, let me explain over linkedin later and give you official email. Thank you for your attention!

Re: Count records based on past dates

marianob — Thu, 04 Jun 2020 20:12:10 GMT

It works perfectly! Thanks a lot for your time!

Re: Count records based on past dates

marianob — Thu, 04 Jun 2020 20:13:35 GMT

It works just great as @novinosrin code.

Thanks both for helping me out!

Re: Count records based on past dates

Ksharp — Fri, 05 Jun 2020 11:51:40 GMT

data new;
infile cards expandtabs;
informat date ddmmyy10.;
input id $ date period $;
format date mmddyy10.;  
cards;;;
A	9/12/2019	2019-12
A	9/12/2019	2019-12
A	9/12/2019	2019-12
A	4/11/2019	2019-11
A	19/10/2019	2019-10
A	19/10/2019	2019-10
A	8/10/2019	2019-10
A	20/9/2019	2019-09
A	21/8/2019	2019-08
A	21/8/2019	2019-08
A	28/7/2019	2019-07
A	26/7/2019	2019-07
B	23/8/2018	2018-08
B	23/8/2018	2018-08
B	9/8/2018	2018-08
B	13/7/2018	2018-07
B	25/6/2018	2018-06
B	11/5/2018	2018-05
B	18/4/2018	2018-04
;;;
run;
proc sql;
create table want as
select *,(select count(*) from new 
 where id=a.id and date<input(a.period,anydtdte.)) as count
 from new as a;
quit;

Re: Count records based on past dates

marianob — Fri, 05 Jun 2020 19:15:02 GMT

Thanks, Ksharp! Your code worked great too!