text date to date format in sas

Accepted Solution Solved
Reply
Contributor BSL
Contributor
Posts: 25
Accepted Solution

text date to date format in sas

hi

I have a data which has dates in text format so is there a way to convert a text date into date format??

-rahul

Attachment

Accepted Solutions
Solution
‎01-20-2015 06:57 AM
Super User
Posts: 9,662

Re: text date to date format in sas

I gotta say  your data is too dirty.

proc import datafile='c:\temp\saledatemt.csv' out=have dbms=csv replace ;

guessingrows=32767;

run;

options datestyle=dmy;

data want;

set have;

_PO_date=input(po_date, anydtdte20.);

Yes_No=ifc(_PO_date=datepart(idpodate),'Y','N');

if prxmatch('/email|on|eamil/i',PO_NO_) then

PO_NO_Date=input(prxchange('s/.*(\d\d\/\d+\/\d+)|(\w+\W+\d+\W+\d+).*/$1/',-1,PO_NO_), anydtdte20.);

format _PO_date PO_NO_Date date.;

run;

Xia Keshan

View solution in original post


All Replies
Super User
Posts: 5,254

Re: text date to date format in sas

Yes, and there is tons of examples and syntax for you at support.sas.com....

Data never sleeps
Contributor BSL
Contributor
Posts: 25

Re: text date to date format in sas

hi linus,

here I attached the complete data in the original post.

1. I have a data in excel which and if i import that sheet into SAS directly it reads dates as text which i want in date format.

2. I need to varify PO Date and idpodate whether these two are same or not.

3. there is a variable PO NO., containing a email date and i want to extract that date into a new variable called PO NO. Date

so how do I do so...

DATA HAVE

PO NO.PO Dateidpodate
p.o:-4500797822 date:-02/04/20134-Feb-134-Feb-13
p.o:-4500797822 date:-02/04/20134-Feb-134-Feb-13
p.o:-1001642415 date:-19.02.201419/02/1419-Feb-14
eamil on 08/1/2013
January 09, 2014  e.mail Santosh Kawale
p.o:-4500047627 date:-15/04/201315/04/1315-Apr-13
p.o:-25088965 date:-05/07/2017-May-137-May-13
P>O:-2300014122 DATE:-30/04/201330/04/1330-Apr-13
p.o:-46004451551 date:-27/01/2014
P.O;-7810000591/0   Date:-03/06/136-Mar-136-Mar-13
p.o:-7810000589/0 date:-03/06/20136-Mar-136-Mar-13
p.o:-7810000590/0 date:-03/06/2013
P.O;-7810000591/0   Date:-03/06/136-Mar-136-Mar-13
p.o;-4400059502 date:-01/01/20141-Jan-141-Jan-14
p.o:-4500063819 date:-21/02/201314/02/1314-Feb-13
email
po:-HIN/JUNG/2013-14/04 date:-27/07/201327/07/1327-Jul-13
p.o:-4530009859   by email on 05/02/20142-May-142-May-14
p.O-4077001839 date:-17/12/201317/12/1317-Dec-13
p.o:-PO-SER/439/13-14 date:-26-DEC-201326/12/1326-Dec-13

DATA WANT

PO NO.PO DateidpodatePO NO. Date
p.o:-4500797822 date:-02/04/20134-Feb-134-Feb-13
p.o:-4500797822 date:-02/04/20134-Feb-134-Feb-13
p.o:-1001642415 date:-19.02.201419/02/1419-Feb-14
eamil on 08/1/201308-01-2013
January 09, 2014  e.mail Santosh Kawale
p.o:-4500047627 date:-15/04/201315/04/1315-Apr-13
p.o:-25088965 date:-05/07/2017-May-137-May-13
P>O:-2300014122 DATE:-30/04/201330/04/1330-Apr-13
p.o:-46004451551 date:-27/01/201427-012014
P.O;-7810000591/0   Date:-03/06/136-Mar-136-Mar-13
p.o:-7810000589/0 date:-03/06/20136-Mar-136-Mar-13
p.o:-7810000590/0 date:-03/06/201303-06-2013
P.O;-7810000591/0   Date:-03/06/136-Mar-136-Mar-13
p.o;-4400059502 date:-01/01/20141-Jan-141-Jan-14
p.o:-4500063819 date:-21/02/201314/02/1314-Feb-13
email
po:-HIN/JUNG/2013-14/04 date:-27/07/201327/07/1327-Jul-13
p.o:-4530009859   by email on 05/02/20142-May-142-May-14
p.O-4077001839 date:-17/12/201317/12/1317-Dec-13
p.o:-PO-SER/439/13-14 date:-26-DEC-201326/12/1326-Dec-13
Super User
Super User
Posts: 7,392

Re: text date to date format in sas

Hi,

Firstly I would recommend not using Excel.  Save your data to a proper data transfer format, CSV, XML, Delimited etc.

You can then write a proper import datastep (plenty of examples on here for that).  This gives you full flexibility in how data is read.  Proc import or transfer from Excel will cause you issues, Excel is not a data transfer standard and allows any old garbage in.

Secondly to your point regarding converting data items.  If you read the data you have given, from a csv these data items would be separated by commas, you can read each one as date, and then for the column do:

data want;

     set have;

     my_date=input(put(the_text_date,anydate.);

run;

Or you could just put in your import program to read that column in as date.  I would imagine that, as in most cases, 90% of your trouble is purely the fact that you are using Excel and proc import (which basically guesses what its supposed to do).

First two search results for "Delimited File Import":

https://communities.sas.com/message/153446#153446

https://communities.sas.com/message/241161#241161

Contributor BSL
Contributor
Posts: 25

Re: text date to date format in sas

Hi RW9,

I imported the same data from a csv file but still getting dates in text format. Moreover if i try to compare PO Date and iddate, it does not allow me to write PO Date with a space.

-rahul

Super User
Super User
Posts: 7,392

Re: text date to date format in sas

I agree with Xia Keshan, your data is quite dirty.  Lots of typos and mismatches.  To convert a text date to numeric use the input(xyz,anydate.) or a format you know.  So:

data want;

     set have;

     if po_date ne "" then new_date=input(po_date,anydate.);

run;

Contributor BSL
Contributor
Posts: 25

Re: text date to date format in sas

thanks xia :-)

Solution
‎01-20-2015 06:57 AM
Super User
Posts: 9,662

Re: text date to date format in sas

I gotta say  your data is too dirty.

proc import datafile='c:\temp\saledatemt.csv' out=have dbms=csv replace ;

guessingrows=32767;

run;

options datestyle=dmy;

data want;

set have;

_PO_date=input(po_date, anydtdte20.);

Yes_No=ifc(_PO_date=datepart(idpodate),'Y','N');

if prxmatch('/email|on|eamil/i',PO_NO_) then

PO_NO_Date=input(prxchange('s/.*(\d\d\/\d+\/\d+)|(\w+\W+\d+\W+\d+).*/$1/',-1,PO_NO_), anydtdte20.);

format _PO_date PO_NO_Date date.;

run;

Xia Keshan

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 1285 views
  • 0 likes
  • 4 in conversation