topic Re: Identify Missing Date Records in SAS Programming

Identify Missing Date Records

sas51 — Thu, 03 Mar 2022 05:56:17 GMT

Hi all, I'm working on a data that studies people who has come to the library. The data has two fields, Name and Date.

Example of the data:
Name Date
Ben 31Dec2013
Ben 31Jan2014
Ben 31Mar2014
Ben 30Apr2014
Ben 30Jun2014
Ken 31Mar2014
Ken 31May2014

I would like SAS to tell me which date that a visitor had not visited from their continuous trip since their first record until their last record, hence using above as an example, I will need SAS to tell me Ben did not visit on Feb2014 and May2014 and Ken did not visit on Apr2014.

Thanks in advance.

Re: Identify Missing Date Records

japelin — Thu, 03 Mar 2022 08:10:58 GMT

I'm merging them once I've made the list.

data have;
input Name $ Date:date9.;
format date date9.;
datalines;
Ben 31Dec2013
Ben 31Jan2014
Ben 31Mar2014
Ben 30Apr2014
Ben 30Jun2014
Ken 31Mar2014
Ken 31May2014
;
run;

proc sort data=have out=sorted;
  by Name Date;
run;

data datelist_main;
  set sorted;
  by Name Date;
  if first.name or last.name;
run;
data datelist(rename=(dt=DATE));
  set datelist_main;
  by Name Date;
  retain dt;
  if first.name then do;
    dt=date;
    output;
  end;
  if last.name then do;
    do while(dt<Date);
      dt=intnx('month',dt,1,'end');
      output;
    end;
  end;
  drop date;
run;


data want;
  merge datelist(in=inA) have(in=inB);
  by name date;
  if inA and not(inB);
run;

Re: Identify Missing Date Records

Kurt_Bremser — Thu, 03 Mar 2022 08:47:34 GMT

Make sure that the "have" dataset is sorted by name and date, then do a "look-ahead":

data want;
merge
  have
  have (
    firstobs=2
    rename=(
      name=_name
      date=_date
    )
  )
;
output;
if name = _name
then do;
  date = intnx('month',date,1,'e');
  do while (date lt _date);
    output;
    date = intnx('month',date,1,'e');
  end;
end;
drop _:;
run;

Re: Identify Missing Date Records

Ksharp — Thu, 03 Mar 2022 12:17:44 GMT

data have;
input Name $ Date:date9.;
format date date9.;
datalines;
Ben 31Dec2013
Ben 31Jan2014
Ben 31Mar2014
Ben 30Apr2014
Ben 30Jun2014
Ken 31Mar2014
Ken 31May2014
;
run;

data want;
merge have have(rename=(name=_name date=_date) firstobs=2);
if name=_name then do;
 do i=1 to intck('month',intnx('month',date,0),intnx('month',_date,0)-1);
   want_date=intnx('month',date,i);output;
 end;
end;
format want_date date9.;
keep name want_date;
run;