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.
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;
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;
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.