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
Where does this row in your output come from?
2 18AUG2017 IR
It wasn't in the input.
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;
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;
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
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;
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:
Thanks very much to those who replied!!!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.