topic Re: how to validate datetime column? in SAS Programming

how to validate datetime column?

Helannivas — Thu, 04 Jul 2013 16:50:39 GMT

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.

Re: how to validate datetime column?

sunilzood — Fri, 05 Jul 2013 05:49:30 GMT

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;

Re: how to validate datetime column?

Ksharp — Fri, 05 Jul 2013 06:41:36 GMT

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

Re: how to validate datetime column?

sunilzood — Fri, 05 Jul 2013 07:08:23 GMT

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

Re: how to validate datetime column?

Patrick — Sat, 06 Jul 2013 01:36:59 GMT

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.

Re: how to validate datetime column?

Helannivas — Sat, 06 Jul 2013 04:03:34 GMT

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