topic Re: convert character field to date and compare in SAS Programming

convert character field to date and compare

dht115 — Tue, 29 Mar 2022 21:17:53 GMT

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.

Re: convert character field to date and compare

ballardw — Tue, 29 Mar 2022 22:27:20 GMT

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?

Re: convert character field to date and compare

dht115 — Wed, 30 Mar 2022 00:33:18 GMT

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.

Re: convert character field to date and compare

Kurt_Bremser — Wed, 30 Mar 2022 09:50:46 GMT

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.

Re: convert character field to date and compare

Ksharp — Wed, 30 Mar 2022 11:59:42 GMT

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;