IF THEN ELSE for date format check

[jayakumarmm]

I am trying to do format check on fields to convert into date if it is not date. It is woking fine for numeric fields but the problem  is even considering date fields and  converting into incorrect date.

In the below code, above two dt1 & dt2 are working fine but problem with dt3.

Data work.dataset1;
set work.dataset2;
if verify(trim(left(date1)),'0123456789')=0 Then
do;
dt1= date1- 21916;
 format dt1 mmddyy10.; 
end;
if verify(trim(left(date2)),'0123456789')=0 Then
do;
dt2 = date2- 21916;
 format dt2 mmddyy10.; 
end;
if verify(trim(left(date3)),'0123456789')=0 Then
do;
dt3= date3- 21916;
 format dt3 mmddyy10.; 
end;
run;

[rivieralad]

Hi
You need to provide examples of the input data, i.e. what you have.
Without knowing what dt3 looks like no one will be able to help.
Cheers
Chris

[jayakumarmm]

Hi,
Given below are the input data
dt1 = 42521
dt2 = 42496
dt3 = 05/23/2016

Thanks

[RW9]

I am afraid your question is not clear.  A format statement applies to the whole column, regardless of individual cells.  Also, what is the input data here, it looks like your looking at a character varaible, but removing a number from it?   Is this data read in from Excel by any chance?  You might be able to use the anydate. format to read it in:

data work.dataset1;
  set work.dataset2;
  dt1=input(date1,anydate.);
  dt2=input(date2,anydate.);
  dt3=input(date3,anydate.);
  format dt1 dt2 dt3 mmddyy10.; 
run;
          

As @rivieralad has said post test data, in the form of a datastep, and what the output should look like.

[jayakumarmm]

The input data comes for excel worksheet, given below is the Input date:

dt1 = 42521
dt2 = 42496
dt3 = 05/23/2016

[Kurt_Bremser · Accepted solution]

Look at this:

data have;
input date_text $;
cards;
42521
42496
05/23/2016
;
run;

data want;
set have;
format date_sas mmddyy10.;
if notdigit(substr(date_text,1,length(date_text))) > 0
then date_sas = input(date_text,mmddyy10.);
else date_sas = input(date_text,5.) - 21916;
run;

Instead of the rather complicated approach with notdigit() you might check for

if substr(date_text,3,1) = '/'

[RW9]

Why is data in two different formats, is this what it looks like in Excel, if so fix the Excel file.  

[jayakumarmm]

Excel files are source files for this process and I don't have any control for modifying these files.

[Kurt_Bremser]

That's why Excel is considered an inadequate format for data transfer. Par with having data hammered into stone with a chisel.

[ballardw]

---

@Kurt_Bremser wrote:

That's why Excel is considered an inadequate format for data transfer. Par with having data hammered into stone with a chisel.

---

Actually Kurt I would say it is the other way around. When you hammer into stone you know you can't move anything and therefore think about what goes where before setting chisel to stone. Excel allows stuff anywhere with no consistency and ease of moving things around means people do so without thinking about other considerations.

[RW9]

I agree with @Kurt_Bremser, it is one of the many, many reasons not to use Excel as a data source.  However, it is also a systematic problem.  Your "source data" is wrong, someone somewhere has not done their job correctly and your taking responsibility for fixing it.  If what you do is wrong it will be your fault.  Also, if someone else comes along, they then also have to figure out that incorrect data, maybe they have other ideas on how it should be fixed etc.  

I can understand that sometimes there is push back on this, in my role we have argued for many years now with the database people that they don't want to open a database after lock to make updates.  We have just put a policy in place which clearly states we as recipients of the data make no changes at all without very high level approval, they now unlock the database and update data correctly.