BookmarkSubscribeRSS Feed
anilgvdbm
Quartz | Level 8

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

7 REPLIES 7
Ksharp
Super User

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

Babloo
Rhodochrosite | Level 12

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

Ksharp
Super User

No. It means at least 5 digits .

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

anilgvdbm
Quartz | Level 8

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

user24feb
Barite | Level 11

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;

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to choose a machine learning algorithm

Use this tutorial as a handy guide to weigh the pros and cons of these commonly used machine learning algorithms.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 7 replies
  • 1325 views
  • 1 like
  • 5 in conversation