Removing rows out of a table given a certain condition

Question

I have the following table "HAVE":

ID	Date
Test_5000_ABC_2022-01	01MAY2020
Test_12345_XYZ_2022-05	15OCT2021
Test_00000_UMX_2022-12	01SEP2021
Test_00000_UMX_2022-12	01DEC2022

The last part of a string in the "ID column" there is always a year and a month delimited by "-", while the column "date" has a date in the "DDMMYYY" format.

Now, I would want to delete all entries from this table where the date from the "ID" column is after the date (after the month and year) in the "date" column and save it as a new table. So, basically, my WANT table would look like this:

ID	Date
Test_00000_UMX_2022-12	01DEC2022

I appreciate any kind of help, as I am very new to SAS. Thank you!

Answer 1

• Extract date from the ID variable
• Align the date to beginning of the month
• Compare as needed

data have;
infile cards dlm='09'x truncover;
input ID : $23. Date : date9.;
cards;
Test_5000_ABC_2022-01   01MAY2020
Test_12345_XYZ_2022-05  15OCT2021
Test_00000_UMX_2022-12  01SEP2021
Test_00000_UMX_2022-12  01DEC2022
;;;;
run;

data want;
set have;

date_id = mdy(input(scan(id, -1, "-_"), 8.) , 1, input(scan(id, -2, "-_"), 8.) );

*check your condition;
if date_id > intnx('month', date, 0, 'b') then flag=1;

*if date_id > intnx('month', date, 0, 'b') then delete;

format date_id date yymmdds10.;

run;

Answer 2

You can use the following condition both in proc sql and in a DATA step:

where input(scan(ID, -1, '_')||'-01', yyyymmdd10.) > Date

The scan takes the fraction from your ID after the last _, without the trainling blanks. The input applies the informat yyyymmdd10. to it.