topic Uninterrupted records with a given value in SAS Programming

Uninterrupted records with a given value

MR1993 — Fri, 01 Dec 2017 19:36:25 GMT

Hello,

I am seeking help. I need to extract records with same TYPE ('NO', in this case) showing up uninterrupted based on each date. It is ok 'NO' showed up with other values, but they have to be uninterrupted with no other value on a differnent date in between (i.e. #6) and 'NO' also had to be the last time it showed up (#2 & 5).

Thank you ver much in advance!!!

id ISSUE_DATE TYPE

2    16OCT2015    BC
2    16OCT2015    NO
2    15FEB2017    CD
2    15FEB2017    NO

5 16JAN2016 NO
5 20FEB2017 NO

6    10DEC2015    NO
6    16MAR2016    CD
6    21NOV2017    NO

7    15DEC2015    NO
7    15MAR2016    NO
7    25NOV2017    BC

Desired output with IDS 6 & 7 removed.

id ISSUE_DATE TYPE

2    16OCT2015    BC
2    16OCT2015    NO
2    15FEB2017    CD
2    15FEB2017    NO
2    18AUG2017    IR
5    16JAN2016    NO
5    20FEB2017    NO

Re: Uninterrupted records with a given value

ballardw — Fri, 01 Dec 2017 20:03:16 GMT

Where does this row in your output come from?

2 18AUG2017 IR

It wasn't in the input.

Re: Uninterrupted records with a given value

MR1993 — Fri, 01 Dec 2017 21:04:16 GMT

Right. This line should not be in the output.
2 18AUG2017 IR

Re: Uninterrupted records with a given value

PGStats — Sat, 02 Dec 2017 04:21:44 GMT

This will do it:

data have;
input id   ISSUE_DATE :date9.   TYPE $;
format issue_date date9.;
datalines;
2    16OCT2015    BC 
2    16OCT2015    NO 
2    15FEB2017    CD     
2    15FEB2017    NO 
5    16JAN2016    NO
5    20FEB2017    NO
6    10DEC2015    NO
6    16MAR2016    CD
6    21NOV2017    NO
7    15DEC2015    NO
7    15MAR2016    NO
7    25NOV2017    BC
;

proc sql;
create table want as
select * from have
where id in 
    (select a.id 
     from 
        (select id, count(distinct issue_date) as n 
         from have 
         group by id, type) as a
        inner join 
        (select id, count(distinct issue_date) as n 
         from have 
         group by id) as b
        on a.id=b.id and a.n=b.n);
select * from want;
quit;

Re: Uninterrupted records with a given value

Ksharp — Sat, 02 Dec 2017 11:29:23 GMT

What you gonna do if data like:

7    15DEC2015 BC
7    15MAR2016    NO
7    25NOV2017 NO

data have;
input id   ISSUE_DATE :date9.   TYPE $;
format issue_date date9.;
datalines;
2    16OCT2015    BC 
2    16OCT2015    NO 
2    15FEB2017    CD     
2    15FEB2017    NO 
5    16JAN2016    NO
5    20FEB2017    NO
6    10DEC2015    NO
6    16MAR2016    CD
6    21NOV2017    NO
7    15DEC2015    NO
7    15MAR2016    NO
7    25NOV2017    BC
;

data want;
 if _n_=1 then do;
 if 0 then set have;
  declare hash h();
  h.definekey('ISSUE_DATE');
  h.definedone();
  declare hash hh();
  hh.definekey('ISSUE_DATE','TYPE');
  hh.definedone();
 end;

do until(last.id);
 set have;
 by id;
 h.ref();
 if type='NO' then hh.ref();
end;
if type='NO' and h.num_items=hh.num_items then yes=1;
do until(last.id);
 set have;
 by id;
 if yes then output;
end;
h.clear();
hh.clear();
run;

Re: Uninterrupted records with a given value

MR1993 — Mon, 04 Dec 2017 20:27:00 GMT

Thanks!

When this happens, output the last two rows.

What you gonna do if data like:

7    15DEC2015 BC
7    15MAR2016    NO
7    25NOV2017 NO

Re: Uninterrupted records with a given value

mkeintz — Tue, 05 Dec 2017 02:19:30 GMT

You want to keep all records for any ID in which there are two dates having a type = "NO" record with no intervening dates. This program has two data steps, but processes the data set file only once. Because the first data step creates a data set view, its output will only be generated when a subsequent step refers to that view:

data need (keep=id) /view=need;
  set have (in=in1 where=(type='NO')) have (where=(type^='NO'));
  by id issue_date;
  if first.issue_date;
  consecutive_nos=(in1=1 and lag(in1)=1);
  if consecutive_nos=1 and first.id=0;
run;
  
data want;
  merge have need (in=inkeep);
  by id;
  if inkeep;
run;

Notes:

The data set view NEED reads in data set have with the NO's interleaved with other records in such a way that the first record for a given id/issue_date is a type "NO", assuming one is present for that date.
The subsetting IF filter through only records starting each date (which means all the 'NO' and only some of the others). This also means the LAG test is done only once per date.
Then just test for consecutive no's by using the lag function, and filter out cases in which consecutive no's cross from one ID to the next.
The 2nd step is a normal merge.

Re: Uninterrupted records with a given value

Ksharp — Tue, 05 Dec 2017 12:50:14 GMT

OK. Here is .after that, you can delete the first row which don't have 'NO'.

data have;
input id   ISSUE_DATE :date9.   TYPE $;
format issue_date date9.;
datalines;
2    16OCT2015    BC 
2    16OCT2015    NO 
2    15FEB2017    CD     
2    15FEB2017    NO 
5    16JAN2016    NO
5    20FEB2017    NO
6    10DEC2015    NO
6    16MAR2016    CD
6    21NOV2017    NO
7    15DEC2015    NO
7    15MAR2016    NO
7    25NOV2017    BC
8    15DEC2015    BC
8    15MAR2016    NO
8    25NOV2017    NO
;

data want;
 if _n_=1 then do;
 if 0 then set have;
  declare hash h();
  h.definekey('ISSUE_DATE');
  h.definedone();
  declare hash hh();
  hh.definekey('ISSUE_DATE','TYPE');
  hh.definedone();
 end;
n=0;
do until(last.id);
 set have;
 by id ISSUE_DATE;
 n+first.ISSUE_DATE;
 if n ne 1 then do;
   h.ref();
   if type='NO' then hh.ref();
 end;
end; 
if h.num_items=hh.num_items then yes=1;
do until(last.id);
 set have;
 by id;
 if yes then output;
end;
h.clear();
hh.clear();
run;

Re: Uninterrupted records with a given value

Max503 — Thu, 28 Dec 2017 19:12:08 GMT

Thanks very much to those who replied!!!