i have a dataset of the following kind
ID ReportingDate Start Date Value 001 31Jan2018 31Jan2018 100 001 28Feb2018 31Jan2018 200 001 .... 002 30Sep2016 28Feb2014 400 002 31Oct2016 28Feb2014 500 002 ....
I need to create a condition such that only keep records with valid IDs where first instance of 'Start Date' is equal to or later than first instance of 'Reporting Date'. So essentially, ID 002 should not be kept in the dataset as the first Start Date is earlier than first Reporting Date.
I have been fiddling around with first. but cannot get the condition to work. Any ideas?
data have;
infile cards truncover;
input ID (ReportingDate StartDate) (:date9.) Value ;
format ReportingDate StartDate date9.;
cards;
001 31Jan2018 31Jan2018 100
001 28Feb2018 31Jan2018 200
002 30Sep2016 28Feb2014 400
002 31Oct2016 28Feb2014 500
;
data want;
do until(last.id);
set have;
by id;
if first.id and startdate>=reportingdate then f=1;
if f then output;
end;
drop f;
run;
data have;
infile cards truncover;
input ID (ReportingDate StartDate) (:date9.) Value ;
format ReportingDate StartDate date9.;
cards;
001 31Jan2018 31Jan2018 100
001 28Feb2018 31Jan2018 200
002 30Sep2016 28Feb2014 400
002 31Oct2016 28Feb2014 500
;
proc sql;
create table want(drop=t) as
select *
from
(select * ,min(startdate)>=reportingdate as t from have group by id )
group by id
having max(t)=1;
quit;
data have;
infile cards truncover;
input ID (ReportingDate StartDate) (:date9.) Value ;
format ReportingDate StartDate date9.;
cards;
001 31Jan2018 31Jan2018 100
001 28Feb2018 31Jan2018 200
002 30Sep2016 28Feb2014 400
002 31Oct2016 28Feb2014 500
;
data want;
do until(last.id);
set have;
by id;
if first.id and startdate>=reportingdate then f=1;
if f then output;
end;
drop f;
run;
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.