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;
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
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.
Ready to level-up your skills? Choose your own adventure.