topic Data are not missing at least two consecutive years in SAS Procedures

Data are not missing at least two consecutive years

shawnchen0321 — Wed, 19 Jul 2023 05:52:17 GMT

Hi, all experts.

I have a sample selection criterion which is data are not missing at least two consecutive years.

I have a sample below.

data have;
  input Panelist Year othervars;
  cards;
1          2017 1
1          2019 1
1          2020 1
2          2017 1
2          2020 1
3          2018 1
3          2020 1
;
run;

I want to be like the code below.

data want;
  input Panelist Year othervars;
  cards;
1          2017 1
1          2019 1
1          2020 1
3          2018 1
3          2020 1
;
run;

Does anyone know how to solve this problem?

Thanks in advance.

Re: Data are not missing at least two consecutive years

mkeintz — Wed, 19 Jul 2023 12:33:16 GMT

You have to pass through each panelist twice - once to find gaps, and the second time to reread and output those with no two-year gaps:


data want (drop=_:);
  set have (in=firstpass)  have (in=secondpass);
  by panelist;

  _gap_found + (firstpass=1 and dif(year)>2);
  if first.panelist then _gap_found=0;

  if secondpass and _gap_found=0;
run;

This assumes that the data are sorted by panelist/year.

Editted note: the DIF(x) function is the result of x-LAG(x), except it doesn't generate a "missing values were generated ..." note for the first observation.

Re: Data are not missing at least two consecutive years

shawnchen0321 — Wed, 19 Jul 2023 13:13:18 GMT

It can work. Thanks a lot.