topic Re: Regarding date conversion in SAS Data Science

Regarding date conversion

anilgvdbm — Wed, 07 Jan 2015 11:32:20 GMT

Dear All,

I'm facing one problem in date variable that is in the date variable some values are in date format and some values are in number format like for example 485959 and 12/08/2014 so i need to bring it to one format that is date format so please can anyone help me out in this i will paste an sample data for your reference.

sample data is like this

date_of_interview

29/08/2013

41466

41589

41497

this is from sas file the same data in excel is in correct format.i don't know when i importing to sas its showing like this. please help me out...

your help is highly appreciated.

Thanks and Regards,

Anil

Re: Regarding date conversion

Ksharp — Wed, 07 Jan 2015 11:53:45 GMT

Is that variable character type or numeric type ?

data have;
 input x : $20.;
cards;
29/08/2013
29/08/2013
29/08/2013
29/08/2013
41466
41589
41497
;
run;
data want;
 set have;
 if prxmatch('/\d{5,}/',x) then new=input(x,mmddyy.);
  else new=input(x,anydtdte12.);
 format new ddmmyy8.;
 run;

Xia Keshan

Re: Regarding date conversion

RW9 — Wed, 07 Jan 2015 11:54:27 GMT

Well my first suggestion would be to export your data from Excel into a proper data transfer format file, for example CSV or XML. Excel is not for <insert whatever the use is> task. With a properly defined datastep read in of a CSV you will not get this type of problem. What I would imagine is the case is that the format in Excel file is not consistent across cells (hence a good reason why not to use as a data transfer/base/warehouse).

You can create some conditionals to try to convert it in SAS, however would you really want try to handle all the rubbish Excel can throw out at you? What if someone puts in one cell the value 123456789 instead of a date, what happens then?

data temp;

d="29/08/2013"; output;

d="41466"; output;

d="41589"; output;

d="41497"; output;

run;

data temp;

set temp;

if length(strip(d)) < 10 then new_date=input(d,best.);

else new_date=input(d,ddmmyy10.);

format new_date date9.;

run;

Re: Regarding date conversion

anilgvdbm — Fri, 23 Jan 2015 05:03:49 GMT

Dear RW9,

Here actual problem is the data is entered by the CSPro software .. then i will export from CS pro to SAS direclty.

my data entry operates when they are entering the date variable the mistake they have done is

for example date is 9/12/2014 and 10/12/2014 in between these two dates the length is varies in the first date the length is 9 and 2nd one is 10 so when i import this date into SAS the 2nd date will import as a date and 1st date will import as a sas date number like 45983 .... so what is the exact solution for this problem.

Regards,

Anil

Re: Regarding date conversion

user24feb — Fri, 23 Jan 2015 06:11:31 GMT

A very simple solution could be:

data temp;
d="29/08/2013"; output;
d="29/08/2013"; output;
d="29/08/2013"; output;
d="9/12/2014"; output;
d="10/1/2014"; output;
d="1/1/2015"; output;
d="41466"; output;
d="41589"; output;
d="41497"; output;
run;

Data Want;
Set temp;
If Find(d,'/') Then Date=MDY(Put(Scan(d,2,'/'),2.),Put(Scan(d,1,'/'),2.),Put(Scan(d,3,'/'),4.));
Else Date=Put(d,6.)-21916; * your dates start at 1.1.1900 ?;
Format Date Date9.;
Run;

Re: Regarding date conversion

RW9 — Fri, 23 Jan 2015 09:15:57 GMT

Just to note, you can shrink your put's down by using anydate:

if index(d,"/") > 0 then new_date=datepart(input(d,anydtdtm.));

Re: Regarding date conversion

Babloo — Fri, 23 Jan 2015 09:25:14 GMT

Is {5,} in your PRXmatch function denotes 5 digits?

Re: Regarding date conversion

Ksharp — Sat, 24 Jan 2015 06:47:21 GMT

No. It means at least 5 digits .