topic Re: Date Column with Gap Greater than 1 Year in SAS Programming

Date Column with Gap Greater than 1 Year

BenBen — Wed, 14 Nov 2018 20:34:43 GMT

Hi, I have a cross-sectional dataset that contains the company name, company id, and report date. However, some observations may have a report date gap of more than one year and I want to figure out what company has a report date gap of more than one year. I am a beginner in SAS programming...and have no idea how to handle this. Any suggestion....? Thanks.

My orginial dataset look like this:

Name	ID	Rdate
AAA	110	31DEC2000
AAA	110	31MAR2001
AAA	110	30JUN2001
AAA	110	30SEP2001
AAA	110	31DEC2001
AAA	110	31MAR2002
AAA	110	30JUN2002
BBB	111	31DEC2000
BBB	111	31MAR2002
CCC	112	31MAR2001
CCC	112	30SEP2001
CCC	112	31DEC2001
DDD	113	31DEC2000
DDD	113	31MAR2001
DDD	113	30JUN2002

Want:

Name	ID	Rdate	Gap
AAA	110	31DEC2000
AAA	110	31MAR2001
AAA	110	30JUN2001
AAA	110	30SEP2001
AAA	110	31DEC2001
AAA	110	31MAR2002
AAA	110	30JUN2002
BBB	111	31DEC2000	Y
BBB	111	31MAR2002	Y
CCC	112	31MAR2001
CCC	112	30SEP2001
CCC	112	31DEC2001
DDD	113	31DEC2000
DDD	113	31MAR2001	Y
DDD	113	30JUN2002	Y

Y indicates those observations that have a report date gap of more than one year.

Re: Date Column with Gap Greater than 1 Year

PGStats — Wed, 14 Nov 2018 20:55:55 GMT

A data step way to do this:

data want;
do until(last.ID);
    set have; by ID Rdate;
    if not missing(pDate) then 
        if intck("year", pDate, rDate, "Continuous") > 0 then Gap = "Y";
    pDate = rDate;
    end;
do until(last.ID);
    set have; by ID;
    output;
    end;
drop pDate;
run;

Re: Date Column with Gap Greater than 1 Year

Ksharp — Thu, 15 Nov 2018 13:16:21 GMT

data have;
infile cards expandtabs;
input Name $ 	ID 	Rdate : date9.;
format rdate date9.;
cards;
AAA	110	31DEC2000
AAA	110	31MAR2001
AAA	110	30JUN2001
AAA	110	30SEP2001
AAA	110	31DEC2001
AAA	110	31MAR2002
AAA	110	30JUN2002
BBB	111	31DEC2000
BBB	111	31MAR2002
CCC	112	31MAR2001
CCC	112	30SEP2001
CCC	112	31DEC2001
DDD	113	31DEC2000
DDD	113	31MAR2001
DDD	113	30JUN2002
;
run;
data temp;
 set have;
 if name=lag(name) and rdate>intnx('year',lag(rdate),1,'s') then output;
run;
proc sql;
create table want as
 select *,case when exists(select * from temp where name=a.name) then 'Y' else ' ' end as flag
  from have as a;
quit;