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
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;
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;
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.