DATA Step, Macro, Functions and more

Uninterrupted records with a given value

Reply
New Contributor
Posts: 3

Uninterrupted records with a given value

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

Super User
Posts: 13,084

Re: Uninterrupted records with a given value

Where does this row in your output come from?

2    18AUG2017    IR

 

It wasn't in the input.

New Contributor
Posts: 3

Re: Uninterrupted records with a given value

Right. This line should not be in the output.
2 18AUG2017 IR
Esteemed Advisor
Posts: 5,409

Re: Uninterrupted records with a given value

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;

PG
Super User
Posts: 10,626

Re: Uninterrupted records with a given value

[ Edited ]

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;

 

New Contributor
Posts: 3

Re: Uninterrupted records with a given value

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

 

 

Super User
Posts: 10,626

Re: Uninterrupted records with a given value

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;
Trusted Advisor
Posts: 1,297

Re: Uninterrupted records with a given value

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:

 

  1. 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.
  2. 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.
  3. 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.
  4. The 2nd step is a normal merge.
New Contributor
Posts: 4

Re: Uninterrupted records with a given value

Thanks very much to those who replied!!!   

Ask a Question
Discussion stats
  • 8 replies
  • 176 views
  • 1 like
  • 6 in conversation