topic Re: Subsetting Dataset based on conditions in SAS Programming

Subsetting Dataset based on conditions

eemrun — Tue, 26 Feb 2019 03:24:05 GMT

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?

Re: Subsetting Dataset based on conditions

novinosrin — Tue, 26 Feb 2019 03:50:42 GMT


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;

Re: Subsetting Dataset based on conditions

novinosrin — Tue, 26 Feb 2019 04:22:04 GMT

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;

Re: Subsetting Dataset based on conditions

eemrun — Wed, 27 Feb 2019 22:41:20 GMT

perfect! thank you!