convert character field to date and compare

[dht115]

Hello, 

How to compare partial date with actual date in sas.

First I need to clean the date_have variable - by removing UN UNK and 00. 

data TEST;
   input date_have $ 9. date_compare $ 9.;
   datalines;
00JAN2020 10NOV2020
UNJAN2020 05DEC2020
UNUNK2020 16DEC2019
UNJUN2020 14FEB2021
01JAN2020 01MAY2020
20JUN2020 01JAN2020
;
run;
*Clean the date_have variable - by removing UN UNK and 00. 
DATA TEST1;
SET TEST;
/* DELETE 00, UN, UNK BY USING PRXCHANGE FUNCTION */
NEW_have=prxchange('s/^UN|^00|UNK//i',-1,date_have);
RUN;

I need to compare new_have variable to date_compare variable. 

Compare Criteria: 

• new_have <=date_compare

When I run above code, I need to remove Observation 3 and Observation 6.

Observation 3 - compare date is in 2019 and our original date is in 2020. 

Observation 6 - compare date is in JAN2020 and our original date is in JUN2020. 

[ballardw]

Just what do you compare a value of 2020 to?

Jun2020?

We need the rules for all of the possible malformed "date_have" and how you intend to consider the value less than or equal.

First step: Your "date_compare" needs to be an actual SAS date value. If not then the first step is to convert it. Informat DATE9.

NEXT will be to pull pieces out of the remaining malformed values. But without knowing how you determine of Jun2020 <= 10Jun2020 I'm not going to bother to guess.Is your compare criteria to KEEP records when New_have <= Date_Compare or REMOVE them?

[dht115]

If date_have is 9 character long (DDMMMYYYY) than need to compare date_have with date_compare

If date_have is 7 character long (MMMYYYY) than need to compare date_have with date_compare (only MMMYYYY) portion.

If date_have is 4 character long (YYYY) than need to compare date_have with date_compare (only YYYY) portion.

[Kurt_Bremser]

What do you mean by "compare". According to these rules:

---

@dht115 wrote:

If date_have is 9 character long (DDMMMYYYY) than need to compare date_have with date_compare

If date_have is 7 character long (MMMYYYY) than need to compare date_have with date_compare (only MMMYYYY) portion.

If date_have is 4 character long (YYYY) than need to compare date_have with date_compare (only YYYY) portion.

---

all your observations would need to be removed, as complete dates do not match exactly, and the partial dates do also not match with their counterparts.

[Ksharp]

data TEST;
   input date_have :$20. date_compare : $20.;
   datalines;
00JAN2020 10NOV2020
UNJAN2020 05DEC2020
UNUNK2020 16DEC2019
UNJUN2020 14FEB2021
01JAN2020 01MAY2020
20JUN2020 01JAN2020
;
run;
DATA TEST1;
SET TEST;
/* DELETE 00, UN, UNK BY USING PRXCHANGE FUNCTION */
NEW_have=prxchange('s/^UN|^00|UNK//i',-1,date_have);

if length(NEW_have)=9 then do;
 base=input(strip(NEW_have),date9.);
 compare=input(strip(date_compare),date9.);
end;

if length(NEW_have)=7 then do;
 base=input(strip(NEW_have),monyy7.);
 compare=input(strip(substr(date_compare,3)),monyy7.);
end;

if length(NEW_have)=4 then do;
 base=input(strip(NEW_have),best.);
 compare=input(strip(substr(date_compare,6)),best.);
end;

flag=(base<=compare) ;

drop base compare;
RUN;