sas enumeration variable for next 6 months

[sebster24]

Hello,

I have made a similar query earlier, but since the topic has been closed off, i have to restart again as I found even more complications.

DATA: The input data is unsorted and hence I am using hash tables to take the input data, do some iterations, sort and then output.

What I want: I am trying to enumerate a table variable "answer" with binary values (0/1) if variable filter = "Y" for the next 6 month observations with the same client. In some instances, the client is missing from some monthly observations eg: client FG5151 is missing from September and October 2006. In short if variable filter "Y" then this observation and the next 6 months observations for same client should be assigned variable "answer" eq 1, else 0.

Also, I would like to highlight which end of month observations the client is missing (table : WANT2). eg: for client FG5151, i should have a table noting what months the client is missing.

data have;
input client $ dates date9. filter $;
datalines ;
Fg5151 28.Feb.06 N
Fg5151 31.Mar.06 N
Fg5151 30.Apr.06 N
Fg5151 31.May.06 Y
Fg5151 30.Jun.06 N
Fg5151 31.Jul.06 Y
Fg5151 31.Aug.06 N
Fg5151 30.Nov.06 N
Fg5151 31.Dec.06 N
Fg5151 31.Jan.07 N
A101 28.Feb.06 N
A101 31.Mar.06 N
A101 30.Apr.06 Y
A101 31.May.06 N
A101 30.Jun.06 N
A101 31.Jul.06 N
ABC123 31.Mar.06 N
;



data want;
input client $ dates date9. filter $ answer $;
datalines ;
A101 28.Feb.06 N 0
A101 31.Mar.06 N 0
A101 30.Apr.06 Y 1
A101 31.May.06 N 1
A101 30.Jun.06 N 1
A101 31.Jul.06 N 1
ABC123 31.Mar.06 N 0
Fg5151 28.Feb.06 N 0
Fg5151 31.Mar.06 N 0
Fg5151 30.Apr.06 N 0
Fg5151 31.May.06 Y 1
Fg5151 30.Jun.06 N 1
Fg5151 31.Jul.06 Y 1
Fg5151 31.Aug.06 N 1
Fg5151 30.Nov.06 N 1
Fg5151 31.Dec.06 N 1
Fg5151 31.Jan.07 N 0
;

data want2;
input client $ dates date9. filter $;
datalines ;
Fg5151 30.Sep.06 N 1
Fg5151 30.Oct.06 N 1
;

User RW9 had solved a similar issue earlier, but by counting the number of observations instead of months:

data want;
  set have;
  retain answer c;
  if _n_=1 or lag(client) ne client then do;
    answer=0;
    c=0;
  end;
  if filter="Y" then do;
  	call symput('xdate',dates);
    answer=1;
    c=1;
  end;
  else if answer=1 then c=c+1;
  if (intnx("month",dates,6,"same")) then do;
    answer=0;
    c=0;
  end;
run;

My brain is totally fried from trying a solution.

I would be really grateful if someone could help me out in this endeavour.

thanks.

sebastian

[Ksharp]

data have;
input client $ dates : date9. filter $;
year=year(dates);
month=month(dates);
format dates date9.;
datalines ;
Fg5151 28.Feb.06 N
Fg5151 31.Mar.06 N
Fg5151 30.Apr.06 N
Fg5151 31.May.06 Y
Fg5151 30.Jun.06 N
Fg5151 31.Jul.06 Y
Fg5151 31.Aug.06 N
Fg5151 30.Sep.06 N
Fg5151 31.Oct.06 N
Fg5151 30.Nov.06 N
Fg5151 31.Dec.06 N
Fg5151 01.Jan.07 N
A101 28.Feb.06 N
A101 31.Mar.06 N
A101 30.Apr.06 Y
A101 31.May.06 N
A101 30.Jun.06 N
A101 31.Jul.06 N
ABC123 31.Mar.06 N
;
run;
data key;
 set have(where=(filter='Y'));
 output;
 do i=1 to 5;
  dates=intnx('month',dates,1);
  year=year(dates);
  month=month(dates);
  output;
 end;
 drop i;
run;
data want;
 if _n_=1 then do;
  declare hash h(dataset:'key',hashexp:20);
  h.definekey('client','year','month');
  h.definedone();
 end;
set have;
if h.check()=0 then answer=1;
 else answer=0;
run;

[Ksharp · Accepted solution]

data have;
input client $ dates : date9. filter $;
year=year(dates);
month=month(dates);
format dates date9.;
datalines ;
Fg5151 28.Feb.06 N
Fg5151 31.Mar.06 N
Fg5151 30.Apr.06 N
Fg5151 31.May.06 Y
Fg5151 30.Jun.06 N
Fg5151 31.Jul.06 Y
Fg5151 31.Aug.06 N
Fg5151 30.Sep.06 N
Fg5151 31.Oct.06 N
Fg5151 30.Nov.06 N
Fg5151 31.Dec.06 N
Fg5151 01.Jan.07 N
A101 28.Feb.06 N
A101 31.Mar.06 N
A101 30.Apr.06 Y
A101 31.May.06 N
A101 30.Jun.06 N
A101 31.Jul.06 N
ABC123 31.Mar.06 N
;
run;
data key;
 set have(where=(filter='Y'));
 output;
 do i=1 to 5;
  dates=intnx('month',dates,1);
  year=year(dates);
  month=month(dates);
  output;
 end;
 drop i;
run;
data want;
 if _n_=1 then do;
  declare hash h(dataset:'key',hashexp:20);
  h.definekey('client','year','month');
  h.definedone();
 end;
set have;
if h.check()=0 then answer=1;
 else answer=0;
run;

[Haikuo]

An alternative without using Hash,

data have;
	input client $ dates date9. filter $;
	format dates date9.;
	datalines;
Fg5151 28.Feb.06 N
Fg5151 31.Mar.06 N
Fg5151 30.Apr.06 N
Fg5151 31.May.06 Y
Fg5151 30.Jun.06 N
Fg5151 31.Jul.06 Y
Fg5151 31.Aug.06 N
Fg5151 30.Nov.06 N
Fg5151 31.Dec.06 N
Fg5151 31.Jan.07 N
A101 28.Feb.06 N
A101 31.Mar.06 N
A101 30.Apr.06 Y
A101 31.May.06 N
A101 30.Jun.06 N
A101 31.Jul.06 N
ABC123 31.Mar.06 N
;

data want want2;
	set have;
	by client notsorted;
	_gap=intck('month',dates,lag(dates));
	_dates=dates;
	format _dates date9.;

	if first.client then
		do;
			_gap=-1;
			_ct=constant('big');
		end;

	do _gap=_gap by 1 to -1;
		dates=intnx('month',_dates,(_gap+1),'e');

		if filter='Y' then
			do;
				_ct=0;
				answer=1;
			end;

		_ct+1;

		if _ct>6 then
			answer=0;

		if _gap <-1 then
			output want2;
		else output want;
	end;

	retain answer;
	drop _:;
run;

[sebster24]

Thank you Haikuo for your input. I wish there was a way to accept multiple solutions.