DATA Step, Macro, Functions and more

IF THEN ELSE for date format check

Accepted Solution Solved
Reply
Contributor
Posts: 55
Accepted Solution

IF THEN ELSE for date format check

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;

Accepted Solutions
Solution
‎09-13-2016 09:19 PM
Super User
Posts: 6,938

Re: IF THEN ELSE for date format check

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) = '/'
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Occasional Contributor
Posts: 19

Re: IF THEN ELSE for date format check

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
Contributor
Posts: 55

Re: IF THEN ELSE for date format check

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

Thanks


Super User
Super User
Posts: 7,401

Re: IF THEN ELSE for date format check

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.

Contributor
Posts: 55

Re: IF THEN ELSE for date format check

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

dt1 = 42521
dt2 = 42496
dt3 = 05/23/2016
Solution
‎09-13-2016 09:19 PM
Super User
Posts: 6,938

Re: IF THEN ELSE for date format check

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) = '/'
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Super User
Posts: 7,401

Re: IF THEN ELSE for date format check

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

Contributor
Posts: 55

Re: IF THEN ELSE for date format check

Excel files are source files for this process and I don't have any control for modifying these files.
Super User
Posts: 6,938

Re: IF THEN ELSE for date format check

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 10,500

Re: IF THEN ELSE for date format check


KurtBremser 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.

Super User
Super User
Posts: 7,401

Re: IF THEN ELSE for date format check

I agree with @KurtBremser, 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.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 734 views
  • 1 like
  • 5 in conversation