Building models with SAS Enterprise Miner, SAS Factory Miner, SAS Visual Data Mining and Machine Learning or just with programming

Regarding date conversion

Reply
Contributor
Posts: 59

Regarding date conversion

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
29/08/2013
29/08/2013
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

Super User
Posts: 10,044

Re: Regarding date conversion

Posted in reply to anilgvdbm

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

Super Contributor
Posts: 436

Re: Regarding date conversion

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

Super User
Posts: 10,044

Re: Regarding date conversion

No. It means at least 5 digits .

Super User
Super User
Posts: 7,984

Re: Regarding date conversion

Posted in reply to anilgvdbm

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="29/08/2013"; output;

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;

Contributor
Posts: 59

Re: Regarding date conversion

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

Super Contributor
Posts: 340

Re: Regarding date conversion

Posted in reply to anilgvdbm

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;

Super User
Super User
Posts: 7,984

Re: Regarding date conversion

Posted in reply to user24feb

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

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

Ask a Question
Discussion stats
  • 7 replies
  • 615 views
  • 1 like
  • 5 in conversation