BookmarkSubscribeRSS Feed
BenBen
Calcite | Level 5

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
AAA11031DEC2000
AAA11031MAR2001
AAA11030JUN2001
AAA11030SEP2001
AAA11031DEC2001
AAA11031MAR2002
AAA11030JUN2002
BBB11131DEC2000
BBB11131MAR2002
CCC11231MAR2001
CCC11230SEP2001
CCC11231DEC2001
DDD11331DEC2000
DDD11331MAR2001
DDD11330JUN2002

 

Want: 

Name ID RdateGap 
AAA11031DEC2000 
AAA11031MAR2001 
AAA11030JUN2001 
AAA11030SEP2001 
AAA11031DEC2001 
AAA11031MAR2002 
AAA11030JUN2002 
BBB11131DEC2000Y
BBB11131MAR2002Y
CCC11231MAR2001 
CCC11230SEP2001 
CCC11231DEC2001 
DDD11331DEC2000 
DDD11331MAR2001Y
DDD11330JUN2002Y

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

2 REPLIES 2
PGStats
Opal | Level 21

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

SAS Innovate 2025: Call for Content

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!

Submit your idea!

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
  • 2 replies
  • 920 views
  • 2 likes
  • 3 in conversation