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

[art297]

@Asileon: Alternatively, run the following and post the results:

proc freq data=client_engage_2;
  tables category date;
run;

Art, CEO, AnalystFinder.com

[ballardw]

---

@Asileon wrote:

Hi @art297

Please find attached the log. I added the changes and still have 0 observations.

Thanks again!

---

It works ever so much better to just copy text from the log and paste into a code box opened with the forum {I} menu icon.

Many users here do not want to open Microsoft files (DOCX, XLSX and others) from unknown sources for a number of reasons and some have organizational policies or security software to block them.

Plus it is easier to have the content in line with comments and not having to open another window.

[Asileon]

Hi @ballardw,

Thank you for the suggestion. I will do that from now on.

[novinosrin]

@Asileon I concur with @ballardw  . But chill it's all good. Relax!  Rather my anxiety and eyesight hurts as the thread gets too long to go back and forth to track the changes in requirement 🙂

[Asileon]

Hi @novinosrin,

I will copy the code directly in the comment box next time. Thanks for the correction!

[novinosrin]

@Asileon A quick question based on the interval,  sorry. Are your intervals exact 180 days for a 6 month or 7 month period or 

data w;
initial_int='01feb2016'd;
serv_deli='02aug2016'd;
num_of_days=intck('days', initial_int,serv_deli);
month_interval=intck('month',initial_int,serv_deli);/*if service delivered took place on 02aug2016 after initial interview 01feb2016, does this qualify or it has to be sameday or 180 days approach?*/
run;

[Asileon]

Hi @novinosrin This will not qualify for a service within 6 months because it is pass six months (182 days)

[art297]

Although it raises a good question: If initial interview is on 01Jan2017 and service is on 01Jul2017, does that count as within 6 months?

Art, CEO, AnalystFinder.com

[art297 · Accepted solution]

In the code, below, I incorporated the same thing as @PGStats suggested for his code:

data client_engage_3 (keep=ClientID category date);
  set client_engage_2;
  by ClientID;
  array reint(999) _temporary_;
  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 lowcase(first(left(category))) eq 'i' then criterion1=date;
  else if not missing(criterion1) then do;
    if missing(criterion2) and lowcase(first(left(category))) eq 's'
     and date le intnx('month',criterion1,6,'sameday')
     then criterion2=date;
    else if lowcase(first(left(last_category))) eq 'r' and 
        lowcase(first(left(category))) eq 's' then do;
         counter1+1;
         reint(counter1)=last_date;
    end;
    else if lowcase(first(left(category))) eq 'r' and counter1 ge 2 then do;
       counter1+1;
       reint(counter1)=date;
    end;
    else if lowcase(first(left(category))) eq 'r' and lowcase(first(left(last_category))) eq 'r' then do;
      call missing(criterion1);
      call missing(criterion2);
      counter1=0;
    end;
  end;
  if last.ClientID and criterion1 and criterion2 and counter1 ge 3 then do;
    counter2=counter1;
    do i=1 to counter1-1;
      if i eq 1 and not(intnx('month',criterion1,5,'sameday')<=
       reint(i)<=intnx('month',criterion1,7,'sameday')) then counter2=0;
      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;

Let me/us know if that works correctly with your real data.

Art, CEO, AnalystFinder.com

[Asileon]

Thank you for the update! I will let you know tomorrow as soon as I try it.

Also, I ran the first code you sent me with the fake data, and it worked we. It did not output the "initial-interview" just the "re-interviews"; I am wondering if it is possible to modify the code as to have it print the initial interview as well.

Thanks!

[art297]

You can output almost any of the records you want by merging the resulting file with the initial file (note: I changed the code to only output ClientIds in client_engage_3).. e.g.:

data client_engage_3 (keep=ClientID);
  set client_engage_2;
  by ClientID;
  array reint(999) _temporary_;
  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 lowcase(first(left(category))) eq 'i' then criterion1=date;
  else if not missing(criterion1) then do;
    if missing(criterion2) and lowcase(first(left(category))) eq 's'
     and date le intnx('month',criterion1,6,'sameday')
     then criterion2=date;
    else if lowcase(first(left(last_category))) eq 'r' and 
        lowcase(first(left(category))) eq 's' then do;
         counter1+1;
         reint(counter1)=last_date;
    end;
    else if lowcase(first(left(category))) eq 'r' and counter1 ge 2 then do;
       counter1+1;
       reint(counter1)=date;
    end;
    else if lowcase(first(left(category))) eq 'r' and lowcase(first(left(last_category))) eq 'r' then do;
      call missing(criterion1);
      call missing(criterion2);
      counter1=0;
    end;
  end;
  if last.ClientID and criterion1 and criterion2 and counter1 ge 3 then do;
    counter2=counter1;
    do i=1 to counter1-1;
      if i eq 1 and not(intnx('month',criterion1,5,'sameday')<=
       reint(i)<=intnx('month',criterion1,7,'sameday')) then counter2=0;
      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;

data want;
  merge client_engage_2 (where=(lowcase(first(left(category))) in ('i','r')))
        client_engage_3 (in=in3);
  by ClientId;
  if in3;
run;

Art, CEO, AnalystFinder.com

[novinosrin]

@Asileon I worked on a solution (strictly not recommending) as I am not playing with someone's production. May be a perspective to look at whenever you are free. I took Art's sample  as he wrote in  " I've added the latest criterion and two new test cases. The code, below, selects ClienIds 2,4,5,7 and 11"

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
8,initial interview,2/29/2016
8,service delivered,8/29/2016
8,re-interview,9/3/2016
8,re-interview,10/3/2016
8,re-interview,11/3/2016
8,re-interview,12/3/2016
8,service delivered,12/4/2016
8,re-interview,6/3/2017
8,service delivered,6/5/2017
8,re-interview,12/2/2017
9,initial interview,2/29/2016
9,service delivered,8/29/2016
9,re-interview,10/3/2016
9,service delivered,10/4/2016
9,re-interview,12/3/2016
9,service delivered,12/4/2016
9,re-interview,6/3/2017
9,service delivered,6/5/2017
9,re-interview,12/2/2017
9,service delivered,12/4/2017
9,re-interview,6/2/2018
9,service delivered,6/3/2018
9,re-interview,6/4/2018
10,initial interview,2/29/2016
10,service delivered,8/29/2016
10,service delivered,10/3/2016
10,service delivered,10/4/2016
10,service delivered,12/3/2016
10,service delivered,12/4/2016
10,service delivered,6/3/2017
10,service delivered,6/5/2017
10,re-interview,12/2/2017
10,service delivered,12/4/2017
10,service delivered,12/5/2017
10,service delivered,12/6/2017
10,re-interview,6/2/2018
10,service delivered,6/3/2018
10,re-interview,12/4/2018
11,initial interview,6/29/2016
11,service delivered,8/29/2016
11,service delivered,10/3/2016
11,service delivered,10/4/2016
11,re-interview,12/2/2016
11,service delivered,12/4/2016
11,service delivered,12/5/2016
11,service delivered,12/6/2016
11,re-interview,6/2/2017
11,service delivered,6/3/2017
11,re-interview,12/4/2017
12,initial interview,4/29/2016
12,service delivered,8/29/2016
12,service delivered,10/3/2016
12,service delivered,10/4/2016
12,re-interview,12/2/2016
12,service delivered,12/4/2016
12,service delivered,12/5/2016
12,service delivered,12/6/2016
12,re-interview,6/2/2017
12,service delivered,6/3/2017
12,re-interview,12/4/2017
;

data want;
if _n_=1 then do; 
if 0 then set have(rename=(date=_date Category=_Category ClientID=_ClientID));
dcl hash h(dataset:'have(rename=(ClientID=_ClientID date=_date Category=_Category))', multidata: 'y');
 h.definekey('_ClientID');
 h.definedata(all:'y');
 h.definedone();
 end;
 array t(100) _temporary_  (100*0) ; /*arbitrary for reinterview capture _temporary_*/
 do until(last.clientid);
 	set have;
 	by ClientID;
	if first.clientid then do;
	call missing(_qual,_flag);
		do _n_=1 to dim(t);
			t(_n_)=0;
		end;
		_reint_count=0;_reint_check=0; _qual_reint=0;_sdl_count=0;
	end;
	if first.clientid and Category='initial interview' then 
		do;
			_flag=1;
 			do while(h.do_over(key:ClientID) eq 0);
				if _qual=. and _Category='service delivered' and (lag(_Category) eq 'initial interview') then  _qual=(_Category='service delivered' and (date<=_date<=intnx('month',date,6,'sameday')));
				if _Category='re-interview' and  (lag(_Category) ne 'service delivered') and _qual=1  then _qual=0;
				else if _Category='re-interview' and  (lag(_Category) eq 'service delivered') and _qual=1 and _reint_count=0 and not(date<=_date<=intnx('month',date,7,'sameday')) then _qual=0;
				else if _Category='re-interview' and (lag(_Category) eq 'service delivered')  and _qual=1 then 
				do;
					_reint_count+1;
					t(_reint_count)=_date;
					if _reint_count>1 then do;
					if intnx('month',t(_reint_count-1),5,'sameday')<=t(_reint_count)<=intnx('month',t(_reint_count-1),7,'sameday') then _reint_check+1;				
					else _reint_check=0;
					if _reint_check=2 then _qual_reint=1;
					end;
				end;
				if _Category='service delivered' and  lag(_Category)='re-interview' and _qual=1 then _sdl_count+1;
			end;
		end;
	if _sdl_count>=2 and _qual_reint=1 and _qual=1 then output;
	*if _sdl_count>=2 and _qual_reint=1 and _qual=1;/* just for testing to see what clients are selected*/
end;
drop _:;
run;