BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
JaySwan
Calcite | Level 5
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
1 ACCEPTED SOLUTION

Accepted Solutions
DBailey
Lapis Lazuli | Level 10
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;

View solution in original post

12 REPLIES 12
ArtC
Rhodochrosite | Level 12
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
Calcite | Level 5
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
Rhodochrosite | Level 12
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
Calcite | Level 5
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
Lapis Lazuli | Level 10
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
Calcite | Level 5
Thanks for the help, all set..
Jay
Pritish
Quartz | Level 8
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
Super User
Format worddate. will help you.


Ksharp
ArtC
Rhodochrosite | Level 12
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_
Fluorite | Level 6
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
Quartz | Level 8 LAP
Quartz | Level 8
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
Obsidian | Level 7

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 🙂

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 12 replies
  • 32396 views
  • 0 likes
  • 8 in conversation