topic Re: Counting the number of observations between rows (same ID) and output the ID that meet the crite in SAS Data Management

Counting the number of observations between rows (same ID) and output the ID that meet the criteria?

Asileon — Sat, 20 Jan 2018 01:35:58 GMT

Hello,

Thanks a lot in advance! Any suggestion would be greatly appreciated.

I have a dataset with multiple observations per Client ID and have to select just the clients that meet the following criteria:

The client must have an initial interview.
The client must have at least one service (cleaning house, delivering groceries, etc.) delivered within 6 months of the initial interview.
The client must have at least 3 re-interviews 5-7 months apart from each other.
The client must have at least one service delivered between the re-interviews.
Get a final dataset with the qualifying Client ID and only the qualifying observations.

Client ID	Category	Date
001	initial interview	02/01/2016
001	service delivered	07/01/2016
001	re-interview	08/01/2016
001	service delivered	10/01/2016
001	re-interview	01/01/2017
001	service delivered	03/01/2017
001	re-interview	08/01/2017
002	initial interview	02/01/2016
002	service delivered	03/01/2016
002	re-interview	08/01/2016
002	service delivered	10/01/2016
002	re-interview	01/01/2017
002	service delivered	03/01/2017
002	re-interview	08/01/2017
003	initial interview	02/01/2016
003	re-interview	08/01/2016
003	service delivered	10/01/2016
003	re-interview	01/01/2017
003	service delivered	03/01/2017
003	re-interview	08/01/2017
004	initial interview	02/01/2016
004	service delivered	03/01/2016
004	re-interview	08/01/2016
004	service delivered	10/01/2016
004	re-interview	01/01/2017
004	service delivered	03/01/2017
004	re-interview	08/01/2017
004	service delivered	09/01/2017
004	re-interview	12/01/2017

The only client meeting the criteria is client 002 and part of client 004. Because:

Client 001 had a service after the initial interview that was delivered after 6 months of the initial interview.
Client 003 had an initial interview and then a re-interview with no service(s) in between
Client 004 had a re-interview (12/01/2017) that was only 4 months from the previous re-interview.

The output should be:

Client ID	Category	Date
002	initial interview	02/01/2016
002	service delivered	03/01/2016
002	re-interview	08/01/2016
002	service delivered	10/01/2016
002	re-interview	01/01/2017
002	service delivered	03/01/2017
002	re-interview	08/01/2017
004	initial interview	02/01/2016
004	service delivered	03/01/2016
004	re-interview	08/01/2016
004	service delivered	10/01/2016
004	re-interview	01/01/2017
004	service delivered	03/01/2017
004	re-interview	08/01/2017
004	service delivered	09/01/2017

Re: Counting the number of observations between rows (same ID) and output the ID that meet the crite

mkeintz — Sat, 20 Jan 2018 03:39:58 GMT

How about putting in a little more effort and generating SAS code for data set generation. And possibly, in addition to text describing record types, you might also include code numbers or letters.

Re: Counting the number of observations between rows (same ID) and output the ID that meet the crite

kiranv_ — Sat, 20 Jan 2018 04:29:21 GMT

your data and questions are confusing to me

you said

The only client meeting the criteria is client 002 and part of client 004. Because:

Client 001 had a service after the initial interview that was delivered after 6 months of the initial interview.

but 002 also has same issue

002	initial interview	02/01/2016
002	service delivered	03/01/2016
002	re-interview	08/01/2016
002	service delivered	10/01/2016

Re: Counting the number of observations between rows (same ID) and output the ID that meet the crite

PGStats — Sat, 20 Jan 2018 05:12:53 GMT

In your example, all dates are on the first day of the month. Do the dates represent months?

Re: Counting the number of observations between rows (same ID) and output the ID that meet the crite

novinosrin — Sat, 20 Jan 2018 05:25:06 GMT

Hi @kiranv_ OP's question is fairly clear as far as client 002 is concerned.I had a look at this question when i was about to leave my college lab and it's pretty straight forward except the part qualification(eg client 004). However since my own non sas assignments in school that's due in for next week is in a mess, I am afraid I didn't bother though excited.

Anyway, to clarify client 002

Client 002 had the first aka initial interview on 02/01/2016

The client must have at least one service (cleaning house, delivering groceries, etc.) delivered within 6 months of the initial interview-- Client 002 had the first serivice post the initial interview on 03/01/2016 which is the subsequent month and that falls within th condition stated above
The client must have at least 3 re-interviews 5-7 months apart from each other---intck aka intevals between the 3 re interviews for client002 do fall in the range >=5 and <=7
The client must have at least one service delivered between the re-interviews.- self explanatory just by looking at data

@Asileon What i wanna you to clarify is- "Part of an id that qualifies as in the case of 004"

1. If i were to pick only those id's that qualify on the basis of satisfying all the conditions, the "part of client004" concerns me, that is it means like pick the obs in all ids that qualify and leave the rest?????? or is it pick only those id's that satisfy "all" conditions

2.Are you using SAS 9.4, this is important to me for me to attempt using more short cuts

3. I am doing you a favor by creating data have as follows:

data have;
input ClientID Category & $20. Date : mmddyy10.;
format date mmddyy10.;
datalines;
1	initial interview	2/1/2016
1	service delivered	7/1/2016
1	re-interview	8/1/2016
1	service delivered	10/1/2016
1	re-interview	1/1/2017
1	service delivered	3/1/2017
1	re-interview	8/1/2017
2	initial interview	2/1/2016
2	service delivered	3/1/2016
2	re-interview	8/1/2016
2	service delivered	10/1/2016
2	re-interview	1/1/2017
2	service delivered	3/1/2017
2	re-interview	8/1/2017
3	initial interview	2/1/2016
3	re-interview	8/1/2016
3	service delivered	10/1/2016
3	re-interview	1/1/2017
3	service delivered	3/1/2017
3	re-interview	8/1/2017
4	initial interview	2/1/2016
4	service delivered	3/1/2016
4	re-interview	8/1/2016
4	service delivered	10/1/2016
4	re-interview	1/1/2017
4	service delivered	3/1/2017
4	re-interview	8/1/2017
4	service delivered	9/1/2017
4	re-interview	12/1/2017
;

PS the bold is just make it look distinct and neat and not to shout 🙂

Re: Counting the number of observations between rows (same ID) and output the ID that meet the crite

Asileon — Sat, 20 Jan 2018 13:08:14 GMT

Hi @mkeintz,

Thanks for your feedback. I have been able to create a flag for the re-interviews that are 5-7 months from the previous. The main issue that I am having is using SAS to output just the client IDs that have a re-interview following by another with at least one services in between.

Thanks

Re: Counting the number of observations between rows (same ID) and output the ID that meet the crite

Asileon — Sat, 20 Jan 2018 13:13:18 GMT

Hi @kiranv_

Client 002 qualify because this client had a service delivered on 03/01/2016 which is within 6 months of the initial interview (one of the criteria). The client will continue to have services even after 6 months of the initial interview, and that is why you see another service 6 months past the initial interview.

002	initial interview	02/01/2016
002	service delivered	03/01/2016
002	re-interview	08/01/2016
002	service delivered	10/01/2016

Thanks

Re: Counting the number of observations between rows (same ID) and output the ID that meet the crite

Asileon — Sat, 20 Jan 2018 13:27:23 GMT

Hi @novinosrin,

Thanks for the feedback!

Answering your questions:

@Asileon What i wanna you to clarify is- "Part of an id that qualifies as in the case of 004"

Answer: I want to pick only those id's that satisfy "all" conditions, be able to create a dataset with only those id's, and have the qualifying observations in that dataset. In the example I posted, the dataset will have:

- client 002, the initial interview, the service(s) within the 6 months after initial interview, the 3 re-interview 5-7 months from the previous, and the service(s) delivered in between the re-interviews.

- Client 004, and the same as above.

2.Are you using SAS 9.4, this is important to me for me to attempt using more short cuts

Answer: Yes, I am using 9.4

3. I am doing you a favor by creating data have as follows:

Answer: Thank you. This is my first posting ever, and I was so focus in creating a good and well explained example of my issue that I forgot. I will try this does not happen in the future.

Re: Counting the number of observations between rows (same ID) and output the ID that meet the crite

Asileon — Sat, 20 Jan 2018 13:32:09 GMT

Hi @PGStats,

The original dataset has millions of observations, and in this example I used imaginary data. This dates represent month/day/year - I used 01 as the day for all of the dates because I tried to make it visually easy for the reader, I was attempting that the reader was able to see the time difference between dates without having to calculate too much.

Thanks

Re: Counting the number of observations between rows (same ID) and output the ID that meet the crite

kiranv_ — Sat, 20 Jan 2018 15:52:57 GMT

for the first criteria 001 also qualifies right because 07/01/2016 also within 6 months and this where I got confused. I am sorry, if this does not make sense.

001	initial interview	02/01/2016
001	service delivered	07/01/2016
001	re-interview	08/01/2016
001	service delivered	10/01/2016

Re: Counting the number of observations between rows (same ID) and output the ID that meet the crite

Asileon — Sat, 20 Jan 2018 16:20:47 GMT

Thank you for the correction, you are right! I apologize, I was focused in including a possible qualify/ no-qualify situation for each client, and I did not do a good job with client 001. Please find below a fix for this, where client 001 DO NOT QUALIFY based on what I said before:

data have;
input ClientID Category & $20. Date : mmddyy10.;
format date mmddyy10.;
datalines;1	initial interview	2/1/2016
1	service delivered	8/25/2016
1	re-interview	8/26/2016
1	service delivered	10/1/2016
1	re-interview	1/1/2017
1	service delivered	3/1/2017
1	re-interview	8/1/2017
2	initial interview	2/1/2016
2	service delivered	3/1/2016
2	re-interview	8/1/2016
2	service delivered	10/1/2016
2	re-interview	1/1/2017
2	service delivered	3/1/2017
2	re-interview	8/1/2017
3	initial interview	2/1/2016
3	re-interview	8/1/2016
3	service delivered	10/1/2016
3	re-interview	1/1/2017
3	service delivered	3/1/2017
3	re-interview	8/1/2017
4	initial interview	2/1/2016
4	service delivered	3/1/2016
4	re-interview	8/1/2016
4	service delivered	10/1/2016
4	re-interview	1/1/2017
4	service delivered	3/1/2017
4	re-interview	8/1/2017
4	service delivered	9/1/2017
4	re-interview	12/1/2017;

Thanks again!

Re: Counting the number of observations between rows (same ID) and output the ID that meet the crite

Asileon — Sat, 20 Jan 2018 16:25:04 GMT

@kiranv_ Please see response above, Thanks

Re: Counting the number of observations between rows (same ID) and output the ID that meet the crite

novinosrin — Sat, 20 Jan 2018 16:59:37 GMT

Good morning from Chicago, Welcome to the SAS forum and thank you for the steadfast communication in clarifying. I'll be right back hopefully with the code or perhaps for some more clarifications.

Re: Counting the number of observations between rows (same ID) and output the ID that meet the crite

Asileon — Sat, 20 Jan 2018 17:10:13 GMT

Good morning!

Thanks a lot @novinosrin, I really appreciate it!

Re: Counting the number of observations between rows (same ID) and output the ID that meet the crite

art297 — Sat, 20 Jan 2018 19:58:48 GMT

I question your logic regarding client 4. They had already met the criteria by the time they had their 3rd re-interview.

If that is correct, then the following code (I think) captures all of your criteria:

data have;
  infile datalines dlm=',';
  informat ClientID 8.;
  informat category $20.;
  informat date mmddyy10.;
  input ClientID Category Date;
  format date mmddyy10.;
  datalines;
1,initial interview,2/1/2016
1,service delivered,8/25/2016
1,re-interview,8/26/2016
1,service delivered,10/1/2016
1,re-interview,1/1/2017
1,service delivered,3/1/2017
1,re-interview,8/1/2017
2,initial interview,2/1/2016
2,service delivered,3/1/2016
2,re-interview,8/1/2016
2,service delivered,10/1/2016
2,re-interview,1/1/2017
2,service delivered,3/1/2017
2,re-interview,8/1/2017
3,initial interview,2/1/2016
3,re-interview,8/1/2016
3,service delivered,10/1/2016
3,re-interview,1/1/2017
3,service delivered,3/1/2017
3,re-interview,8/1/2017
4,initial interview,2/1/2016
4,service delivered,3/1/2016
4,re-interview,8/1/2016
4,service delivered,10/1/2016
4,re-interview,1/1/2017
4,service delivered,3/1/2017
4,re-interview,8/1/2017
4,service delivered,9/1/2017
4,re-interview,12/1/2017
;
/*
 Criterion1: initial interview (criterion_int)
 Criterion2: service within 6 months of initial interview
 Criterion3: 3 re-interviews within 5-7 months of each other,
              with each of the first two re-interviews
              followed by a service
*/
data want;
  set have;
  by ClientID;
  array reint(10);
  retain criterion1 criterion2 reint:;
  last_category=lag(category);
  last_date=lag(date);
  if first.ClientID then do;
    call missing(criterion1);
    call missing(criterion2);
    call missing(last_category);
    call missing(last_date);
    counter1=0;
    counter2=0;
  end;
  if category eq 'initial interview' then criterion1=date;
  else if not missing(criterion1) then do;
    if missing(criterion2) and category eq 'service delivered'
     and date le intnx('month',criterion1,6,'sameday')
     then criterion2=date;
    else if last_category eq 're-interview' and 
        category eq 'service delivered' then do;
         counter1+1;
         reint(counter1)=last_date;
    end;
    else if category eq 're-interview' and counter1 ge 2 then do;
       counter1+1;
       reint(counter1)=date;
    end;
  end;
  if last.ClientID and criterion1 and criterion2 and counter1 ge 3 then do;
    counter2=counter1;
    do i=1 to counter1-1;
      if not(intnx('month',reint(i),5,'sameday')<=
       reint(i+1)<=intnx('month',reint(i),7,'sameday')) then counter2=counter2-1;
    end;
  end;
  if counter2 ge 3;
run;

Art, CEO, AnalystFinder.com

Re: Counting the number of observations between rows (same ID) and output the ID that meet the crite

novinosrin — Sun, 21 Jan 2018 00:03:50 GMT

data have;
input ClientID Category & $20. Date : mmddyy10.;
format date mmddyy10.;
datalines;
1	initial interview	2/1/2016
1	service delivered	8/25/2016
1	re-interview	8/26/2016
1	service delivered	10/1/2016
1	re-interview	1/1/2017
1	service delivered	3/1/2017
1	re-interview	8/1/2017
2	initial interview	2/1/2016
2	service delivered	3/1/2016
2	re-interview	8/1/2016
2	service delivered	10/1/2016
2	re-interview	1/1/2017
2	service delivered	3/1/2017
2	re-interview	8/1/2017
3	initial interview	2/1/2016
3	re-interview	8/1/2016
3	service delivered	10/1/2016
3	re-interview	1/1/2017
3	service delivered	3/1/2017
3	re-interview	8/1/2017
4	initial interview	2/1/2016
4	service delivered	3/1/2016
4	re-interview	8/1/2016
4	service delivered	10/1/2016
4	re-interview	1/1/2017
4	service delivered	3/1/2017
4	re-interview	8/1/2017
4	service delivered	9/1/2017
4	re-interview	12/1/2017
;


/*I have a dataset with multiple observations per Client ID and have to select just the clients that meet the following criteria:

The client must have an initial interview.
The client must have at least one service 
(cleaning house, delivering groceries, etc.) delivered within 6 months of the initial interview.
The client must have at least 3 re-interviews 5-7 months apart from each other.
The client must have at least one service delivered between the re-interviews.
Get a final dataset with the qualifying Client ID and only the qualifying observations.*/


data want;
if _n_=1 then do; 
if 0 then set have(rename=(date=_date Category=_Category));
if 0 then set have(rename=(date=__date Category=__Category));
dcl hash h(dataset:'have(rename=(date=_date Category=_Category))', multidata: 'y');
 h.definekey('ClientID','_Category');
 h.definedata(all:'y');
 h.definedone();
 dcl hash h2(dataset:'have(rename=(date=__date Category=__Category))', multidata: 'y');
 h2.definekey('ClientID');
 h2.definedata(all:'y');
 h2.definedone();
 end;
 _qual=0;
 call missing(_count,_flag);
 do until(last.clientid);
 	set have;
 	by ClientID;
	if first.clientid and Category='initial interview' then 
		do;
 			do while(h2.do_over(key:ClientID) eq 0);
			if __Category='service delivered' and intck('month',date,__date)<6 then _qual=1;
			end;
		end;
	else if Category='re-interview' and _qual=1 then 
		do;
			if h.find(key:clientid,key:Category)=0 then 
				do;
					if 5<=intck('month',date,_date)<=7  then _count+1;
					rc = h.find_next();
					do while(rc = 0);
            			if 5<=intck('month',date,_date)<=7  then _count+1;
            			rc = h.find_next();
         			end;
					if _count>=3 then _qual=1;
				end;
		end;
	else  if Category='service delivered' and  _qual=1 then
		do;
			do while(h2.do_over(key:ClientID) eq 0);
			if  missing(_flag)and __Category='re-interview' and date>__date  then _holddate1=date;
			else if missing(_flag) and __Category='re-interview' and _holddate1<date then _flag=1;
			end;
			if _flag=1 then _qual=1;
		end;
	else _qual=0;
end;
do until(last.clientid);
	set have;
	by clientid;
	if _qual then output;
end;
drop _: rc;
run;

Re: Counting the number of observations between rows (same ID) and output the ID that meet the crite

art297 — Sun, 21 Jan 2018 01:01:37 GMT

@novinosrin: Not a good use of the hash object. Your code takes more than four times longer than my straight forward datastep to run! Plus, given the following example, your code fails to select ClientID 7.

data have;
  infile datalines dlm=',';
  informat ClientID 8.;
  informat category $20.;
  informat date mmddyy10.;
  input ClientID Category Date;
  format date mmddyy10.;
  datalines;
1,initial interview,2/1/2016
1,service delivered,8/25/2016
1,re-interview,8/26/2016
1,service delivered,10/1/2016
1,re-interview,1/1/2017
1,service delivered,3/1/2017
1,re-interview,8/1/2017
2,initial interview,2/1/2016
2,service delivered,3/1/2016
2,re-interview,8/1/2016
2,service delivered,10/1/2016
2,re-interview,1/1/2017
2,service delivered,3/1/2017
2,re-interview,8/1/2017
3,initial interview,2/1/2016
3,re-interview,8/1/2016
3,service delivered,10/1/2016
3,re-interview,1/1/2017
3,service delivered,3/1/2017
3,re-interview,8/1/2017
4,initial interview,2/1/2016
4,service delivered,3/1/2016
4,re-interview,8/1/2016
4,service delivered,10/1/2016
4,re-interview,1/1/2017
4,service delivered,3/1/2017
4,re-interview,8/1/2017
4,service delivered,9/1/2017
4,re-interview,12/1/2017
5,initial interview,2/1/2016
5,service delivered,3/1/2016
5,re-interview,8/1/2016
5,service delivered,10/1/2016
5,re-interview,1/1/2017
5,service delivered,3/1/2017
5,re-interview,8/1/2017
6,initial interview,2/1/2016
6,service delivered,8/2/2016
6,re-interview,8/3/2016
6,service delivered,10/1/2016
6,re-interview,1/1/2017
6,service delivered,3/1/2017
6,re-interview,8/1/2017
7,initial interview,2/29/2016
7,service delivered,8/29/2016
7,re-interview,9/3/2016
7,service delivered,10/1/2016
7,re-interview,2/3/2017
7,service delivered,3/1/2017
7,re-interview,9/1/2017
;

Art, CEO, AnalystFinder.com

Re: Counting the number of observations between rows (same ID) and output the ID that meet the crite

Asileon — Sun, 21 Jan 2018 01:50:09 GMT

Hello @art297,

Thanks a lot for your code!

That is correct, client 004 met the criteria by the third re-interview; all the clients will continue to have services and re-interviews, but I am interested in the initial interview, the following 3 re-interviews and the services within the initial interview/re-interviews.

I will apply your code to my dataset, and let you know how it goes.

Thanks!!

Re: Counting the number of observations between rows (same ID) and output the ID that meet the crite

Asileon — Sun, 21 Jan 2018 01:55:43 GMT

Hello @novinosrin,

Thanks for your code! I will apply it to my dataset also, and see how it goes.

Thanks!

Re: Counting the number of observations between rows (same ID) and output the ID that meet the crite

novinosrin — Sun, 21 Jan 2018 02:07:26 GMT

When hall of famers + Ksharp participate just go for their solution. However, I will work on an alternative to at least satisfy myself when i get to my college lab tomorrow. This forum is very addictive and yet because of whatever other factors my brain didn't seem to focus well enough today. Hmm, one of those days 😞 lol

I made a mistake in loop (do over associative array) too many times and that caused slow execution. I can make it to zip through a key only once and will find ways to match your requirement. Well, after all at least I try and proud to be a contributor here