BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
sebster24
Quartz | Level 8

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

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

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;

View solution in original post

4 REPLIES 4
Ksharp
Super User

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
Super User

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

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
Quartz | Level 8
Thank you Haikuo for your input. I wish there was a way to accept multiple solutions.

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.

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
  • 4 replies
  • 1660 views
  • 1 like
  • 3 in conversation