01JUN2011:00:00:00 Convert To SAS Date...

[JaySwan]

I have a SAS Dataset that originated from Access.
All dates look like 01JUN2011:00:00:00
What is the correct informat synatx to to convert the above format into a SAS Serial Date. Is it datetime18.0 but I cant get it to work.
Thanks, Jay

[ArtC]

try the INPUT function.
[pre]
data dates;
cdate='01JUN2011:10:41:27';
datetime = input(cdate,datetime18.);
date = datepart(datetime);
put cdate=;
put datetime= datetime= datetime18.;
put date= date= date9.;
run;
[/pre]

the LOG shows
[pre]cdate=01JUN2011:10:41:27
datetime=1622544087 datetime=01JUN11:10:41:27
date=18779 date=01JUN2011
[/pre]

[JaySwan]

Thanks for the help...
Your test works fine..

But when I read in the actual variable I get this in the log...

NOTE: Numeric values have been converted to character
values at the places given by: (Line):(Column).
119:27 120:28 121:25
NOTE: Invalid argument to function INPUT at line 119 column 21.
NOTE: Invalid argument to function INPUT at line 120 column 22.
NOTE: Invalid argument to function INPUT at line 121 column 19.

ReqEffectiveDate=01DEC2004:00:00:00
AppReceivedDate=22NOV2004:00:00:00
ActualEffDate=01DEC2004:00:00:00

SAppReceivedDate=.
SReqEffectiveDate=.
SActualEffDate=.

_ERROR_=1 _N_=1



With this code, so there is something SAS does not like...

SAppReceivedDate = input(AppReceivedDate,datetime18.);
SReqEffectiveDate = input(ReqEffectiveDate,datetime18.);
SActualEffDate = input(ActualEffDate,datetime18.);

[ArtC]

Without seeing more of your code and the data it is a bit hard to be definitive. Perhaps the incoming value is not actually character but is already a formatted datetime value. How have you verified that this is not the case. Are the conversion notes being generated by the statements that you have flagged? All that we can be sure of at this point is that something is not what it seems on first glance.

[JaySwan]

Yep, the data in the orig Access dataset is of type date with a length of 8 and looks like this 01DEC2004:00:00:00

Her is my current code, still working on this.
Still not converting the AppReceivedDate into a SAS Serial Date...

data xxx.xxx;
Length CAppReceivedDate $18;
Set xxx.xxx;
CAppReceivedDate = AppReceivedDate; /*Access Date*/
/* format new char var into SAS Dataset Date */
SAppReceivedDate = input(CAppReceivedDate,datetime18.);
Date = Datepart(SAppReceivedDate);
put Date=;
put SAppReceivedDate= SAppReceivedDate=datetime18.;
put Date= date=date9.;
Where Process_Date = 20110512;
run;

[DBailey · Accepted solution]

If you're importing straight from access and its already defined as a date, then you only need to extract the date portion of the datetime variable and format it accordingly. If process_date is a date as well, then you'll need to tell sas that it is..

data xxx.xxx;
Set xxx.xxx;
Date = Datepart(AppReceivedDate);
put Date= date=date9.;
Where Process_Date = '20110512'd;
run;

[JaySwan]

Thanks for the help, all set..
Jay

[Pritish]

I thought the solution might help me with the date format I have but it didn't. So here's what I have:

Wed May 18 16:58:32 2011

How can I get only the Month Date Year ?? For ex: May 18 2011.

Can you please help me out?

[Ksharp]

Format worddate. will help you.


Ksharp

[ArtC]

It is important to understand that the value stored is not the value you "see" if it has a format associated with the variable.

SAS does not have a date variable type. It has variables that are either numeric and character. If it is numeric, and it "looks" like one of the date strings asked about in this thread, then the variable MUST be formatted. You will want to use one of the many functions to break apart a SAS date, datetime, or time value.

SAS stores all dates, times, and datetime values as numbers, just numbers. If it seems to look like something else it either has a format or it is a character string and needs to be converted to an appropriate number (days or seconds). Again a function is used.

[sas_]

Try this

data a2(keep=dt1 dt2);
input dt$ 1-30;

s1=scan(dt,1);
s2=scan(dt,2);
s3=scan(dt,3);
s4=scan(dt,4);
s5=scan(dt,5);
dt1=trim(compress(s3||s2||s5));
dt2=input(dt1,date9.);
format dt2 ddmmyy10.;
cards;
Wed May 18 16:58:32 2011
;
run;
proc print;
run;

[LAP]

To expand on some of the things already said...

It is important to understand how your data is stored and what attributes are associated with a certain variable. This will drive the solution. One way to check your data - there are many - that I use frequently is the following

proc sql;
describe table xxx.xxx;

This will return each column name, type (num or Char), format, informats and label. If the date is already numeric then reformatting with a new date format will do the trick. If the date is stored as a character variable ie Char(n) then you will have to use some sort of conversion to convert to a numeric variable that represents a date. As for the other thread

where mydate = Wed May 18 16:58:32 2011

If this is a FORMATTED numeric value, something like datepart
something like x = datepart(maydate); format datepart worddate.; should give the correct results

[ManoharNath]

Try this;

One more thing always add 'DT' after date time value.

data dates;
date = '01JUN2011:00:00:00'dt;
format date datetime20.;
run;

data test;
set dates;
New_Date = datepart(date);
New_time = timepart(date);
format New_Date date9. New_time hhmm10.2; run;

Thanks 🙂