cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
marianob
Obsidian | Level 7 marianob
Obsidian | Level 7
Go to Solution

Count records based on past dates

Posted 06-04-2020 03:09 PM (1844 views)

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:

IDINQUIRY_DATEPERIOD
A9/12/20192019-12
A9/12/20192019-12
A9/12/20192019-12
A4/11/20192019-11
A19/10/20192019-10
A19/10/20192019-10
A8/10/20192019-10
A20/9/20192019-09
A21/8/20192019-08
A21/8/20192019-08
A28/7/20192019-07
A26/7/20192019-07
B23/8/20182018-08
B23/8/20182018-08
B9/8/20182018-08
B13/7/20182018-07
B25/6/20182018-06
B11/5/20182018-05
B18/4/20182018-04

 

This is what I need:

IDINQUIRY_DATEPERIODAccumulative Inquiry dates
A9/12/20192019-129
A9/12/20192019-129
A9/12/20192019-129
A4/11/20192019-118
A19/10/20192019-105
A19/10/20192019-105
A8/10/20192019-105
A20/9/20192019-094
A21/8/20192019-082
A21/8/20192019-082
A28/7/20192019-070
A26/7/20192019-070
B23/8/20182018-083
B23/8/20182018-083
B9/8/20182018-083
B13/7/20182018-073
B25/6/20182018-062
B11/5/20182018-051
B18/4/20182018-040

 

Any help would be awesome, thanks a lot!

 

 

0 Likes
1 ACCEPTED SOLUTION

Accepted Solutions
smantha
Lapis Lazuli | Level 10 smantha
Lapis Lazuli | Level 10

Re: Count records based on past dates

Posted 06-04-2020 03:44 PM (1777 views)  |   In reply to marianob

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;

View solution in original post

14 REPLIES 14
yabwon
Onyx | Level 15 yabwon
Onyx | Level 15

Re: Count records based on past dates

Posted 06-04-2020 03:17 PM (1829 views)  |   In reply to marianob

What's `per_obs`?

Bart

_______________
Polish SAS Users Group: www.polsug.com and communities.sas.com/polsug

"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings

SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation



marianob
Obsidian | Level 7 marianob
Obsidian | Level 7

Re: Count records based on past dates

Posted 06-04-2020 03:30 PM (1807 views)  |   In reply to yabwon

Hi Bart!

 

Sorry, PER_OB is the same as PERIOD.

0 Likes
PaigeMiller
Diamond | Level 26 PaigeMiller
Diamond | Level 26

Re: Count records based on past dates

Posted 06-04-2020 03:20 PM (1821 views)  |   In reply to marianob

Please explain further the calculations desired.

 

Why is this one a zero

A 28/7/2019 2019-07 0

 

and this one a two?

A 21/8/2019 2019-08 2
--
Paige Miller
0 Likes
marianob
Obsidian | Level 7 marianob
Obsidian | Level 7

Re: Count records based on past dates

Posted 06-04-2020 03:37 PM (1795 views)  |   In reply to PaigeMiller

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!

0 Likes
novinosrin
Tourmaline | Level 20 novinosrin
Tourmaline | Level 20

Re: Count records based on past dates

Posted 06-04-2020 03:36 PM (1800 views)  |   In reply to marianob

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
yabwon
Onyx | Level 15 yabwon
Onyx | Level 15

Re: Count records based on past dates

Posted 06-04-2020 03:40 PM (1785 views)  |   In reply to novinosrin

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

 

Bart

_______________
Polish SAS Users Group: www.polsug.com and communities.sas.com/polsug

"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings

SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation



0 Likes
novinosrin
Tourmaline | Level 20 novinosrin
Tourmaline | Level 20

Re: Count records based on past dates

Posted 06-04-2020 03:45 PM (1773 views)  |   In reply to yabwon

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

yabwon
Onyx | Level 15 yabwon
Onyx | Level 15

Re: Count records based on past dates

Posted 06-04-2020 03:51 PM (1753 views)  |   In reply to novinosrin
Just define "secure for us to access and download" and I gladly help.
Bart
_______________
Polish SAS Users Group: www.polsug.com and communities.sas.com/polsug

"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings

SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation



0 Likes
novinosrin
Tourmaline | Level 20 novinosrin
Tourmaline | Level 20

Re: Count records based on past dates

Posted 06-04-2020 03:53 PM (1747 views)  |   In reply to yabwon

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

0 Likes
marianob
Obsidian | Level 7 marianob
Obsidian | Level 7

Re: Count records based on past dates

Posted 06-04-2020 04:12 PM (1720 views)  |   In reply to novinosrin

It works perfectly! Thanks a lot for your time!

0 Likes
smantha
Lapis Lazuli | Level 10 smantha
Lapis Lazuli | Level 10

Re: Count records based on past dates

Posted 06-04-2020 03:44 PM (1778 views)  |   In reply to marianob

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;
marianob
Obsidian | Level 7 marianob
Obsidian | Level 7

Re: Count records based on past dates

Posted 06-04-2020 04:13 PM (1714 views)  |   In reply to smantha

It works just great as @novinosrin code.

Thanks both for helping me out!

0 Likes
Ksharp
Super User Ksharp
Super User

Re: Count records based on past dates

Posted 06-05-2020 07:51 AM (1611 views)  |   In reply to marianob 
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;
0 Likes
marianob
Obsidian | Level 7 marianob
Obsidian | Level 7

Re: Count records based on past dates

Posted 06-05-2020 03:15 PM (1589 views)  |   In reply to Ksharp

Thanks, Ksharp! Your code worked great too!

0 Likes

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Watch How to Concatenate Values on YouTube

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 14 replies
  • ‎06-04-2020 03:09 PM
  • 1845 views
  • 5 likes
  • 6 in conversation