CODEWITHSUNDEEP
sas
user2448666
user2448666

Reputation: 427

comparing dates with poor entries and replacing with missing values

general question, I have code to compare dates and it is working properly, however, some columns have question marks instead of a date. When my code runs though the columns the question marks should not be affecting the decision, correct? If this is creating an incorrect decision is there a way to replace the ?'s with missing values in all of the columns in my data set?

Data new1;
set new;
format first date MMDDYY10. Last date MMDDYY10.    
first = min(input(serve_date, MMDDYY10.), input(rev_start, MMDDYY10.);
last = max(input(serve_date, MMDDYY10.), input(rev_start, MMDDYY10.);
drop date;
run;

Upvotes: 0

Views: 229

Answers (1)

Joe
Joe

Reputation: 63424

If the date field (character) has a "?" in it, then you will automatically get a missing value when INPUT hits it (and a message in the log).

Data new1;
set new;
format first MMDDYY10. Last MMDDYY10.;
first = min(input(serve_date, ??MMDDYY10.), input(rev_start, ??MMDDYY10.);
last = max(input(serve_date, ??MMDDYY10.), input(rev_start, ??MMDDYY10.);
run;

The ?? tell it to ignore the bad conversions. However, you have another issue: what do do with that missing?

If you want ONE missing to automatically make first or last to be missing, you need to do something like this (depending on the exact details):

Data new1;
set new;
format first MMDDYY10. Last MMDDYY10.;
if (rev_start ne "?" and serve_date ne "?") then do;
  first = min(input(serve_date, ??MMDDYY10.), input(rev_start, ??MMDDYY10.);
  last = max(input(serve_date, ??MMDDYY10.), input(rev_start, ??MMDDYY10.);
end;
run;

If one missing is okay, then you'll need to think about which of first/last should be the missing and which should be the valid value - and set it that way.

Upvotes: 1

Related Questions