BookmarkSubscribeRSS Feed
MR1993
Calcite | Level 5

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

8 REPLIES 8
ballardw
Super User

Where does this row in your output come from?

2    18AUG2017    IR

 

It wasn't in the input.

MR1993
Calcite | Level 5
Right. This line should not be in the output.
2 18AUG2017 IR
PGStats
Opal | Level 21

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
Ksharp
Super User

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;

 

MR1993
Calcite | Level 5

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

 

 

Ksharp
Super User

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;
mkeintz
PROC Star

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.
--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
Max503
Calcite | Level 5

Thanks very much to those who replied!!!   

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 810 views
  • 1 like
  • 6 in conversation