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;
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
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.
Ready to level-up your skills? Choose your own adventure.