hI @ybz12003
One way could be this:
data Have;
length State $5 Week 8;
infile datalines delimiter=',';
input State Week;
datalines;
GA,2101,
GA,2102,
GA,2104,
GA,2105,
GA,2106,
GA,2109,
GA,2110,
GA,2115,
GA,2117,
GA,2118,
GA,2120,
GA,2123,
GA,2124,
NV,2201,
NV,2202,
NV,2204,
NV,2205,
NV,2206,
NV,2209,
NV,2210,
NV,2213,
;
run;
data
want_count (keep=State Count_total)
want_weekgap_search (keep=State Week_start Week_end gap);
;
set have;
by State;
length Count_total Week_start Week_end 8;
if first.State then call missing(of Count_total Week_start Week_end);
lastWeek = lag(Week);
if not first.state then do;
gap = week - lastWeek;
if gap > 2 then do;
Count_total + 1;
Week_start = lastWeek;
Week_end = Week;
output want_weekgap_search;
end;
end;
if last.state then output want_count;
run;
proc sql;
create table want_week_gap
as select
State,
Week_start,
Week_end,
gap
from want_weekgap_search
group by State
having gap = max(gap)
order by
State,
Week_end
;
quit;
Note that you get 3 records in the data set want_week_gap for State = NV, because there are 2 gaps of equal size, and it is not clear which one to choose in this case.
... View more
