how to validate datetime column?

Reply
Frequent Contributor
Posts: 90

how to validate datetime column?

I have some source columns like(For Eg.)

Process_Date

Jun 01 2013 6.40pm

Feb 14 2012 7.25am

Mar 28 2011 3.40pm

I need to validate this column,whether tis date is correct date or not.

Then,I need to load this date column to target as (DD/MM/YYYY). like

01/06/2013

14/02/2012

28/03/2011

How to do this in SAS DI Studio?Kindly help me on this.

Contributor
Posts: 29

Re: how to validate datetime column?

Hi Helann,

Please try ....

data have ;
input test_date $20. ;
cards;
Jun 01 2013 6.40pm
Feb 14 2012 7.25am
Mar 28 2011 3.40pm
;run;

data want (drop = a b c d) ;
set have;
a = compress(upcase(substr(test_date,1,3)));
b = substr(test_date,5,2);
c = substr(test_date,8,4);
  if a = 'JAN' then d = 1;
else if a = 'FEB' then d = 2;
else if a = 'MAR' then d = 3;
else if a = 'APR' then d = 4;
else if a = 'MAY' then d = 5;
else if a = 'JUN' then d = 6;
else if a = 'JUL' then d = 7;
else if a = 'AUG' then d = 8;
else if a = 'SEP' then d = 9;
else if a = 'OCT' then d = 10;
else if a = 'NOV' then d = 11;
else if a = 'DEC' then d = 12;
Final_date  = mdy(d,b,c);
format Final_date ddmmyy10.;
Run;

Super User
Posts: 9,662

Re: how to validate datetime column?

data have ;
input test_date & ?? anydtdtm. ;
date=datepart(test_date);
format date ddmmyy.;
cards;
Jun 01 2013 6.40pm
Feb 14 2012 7.25am
Mar 28 2011 3.40pm
;run;

Ksharp

Contributor
Posts: 29

Re: how to validate datetime column?

Good one Shap, I was expecting for a best Solution... Thanks.!!

Respected Advisor
Posts: 3,886

Re: how to validate datetime column?

Assuming you start with an external file then you could define the Process_Date column with informat "anydtdte." and format "ddmmyy10." in the "external file" object.

Then use a "File Reader" and then a "Data Validation" transformation.

In the "Data Validation" transformation check for missings. All source values for "Process_Date" (the text strings) which couldn't be converted to a SAS date value will be missing.

Frequent Contributor
Posts: 90

Re: how to validate datetime column?

Thanks all for ur reply.

I am facing an issue in all client machines.So I cant able to run any job.

I will try tis ,once the issue is resolved.

Can u  pls help me in resolving this issue.

https://communities.sas.com/thread/47658

Ask a Question
Discussion stats
  • 5 replies
  • 337 views
  • 0 likes
  • 4 in conversation