CODEWITHSUNDEEP
sas
Chris
Chris

Reputation: 103

Removing rows out of a table given a certain condition

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!

Upvotes: 0

Views: 225

Answers (2)

Dirk Horsten
Dirk Horsten

Reputation: 3845

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.

Upvotes: 2

Reeza
Reeza

Reputation: 21264

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

Upvotes: 2

Related Questions