date format

[arpit]

I have a text file that contain below date format

12/12/2000 10:13:45
23/11/2010 9:45:34

I have write below mention data set but it does not comes date format .
data date;
infile'C:\Documents and Settings\sasadm\Desktop\date.txt' dlm='|' dsd;
input x datetime20.;
format datetime20.;
run;


Please help me .

[Cynthia_sas]

Hi:
It clearly says in the documentation that the DATETIME informat requires that the date value to be read must be in the form as described:

The datetime values must be in the following form: ddmmmyy or ddmmmyyyy, followed by a blank or special character, followed by hh:mm:ss.ss (the time).
from this doc site:
http://support.sas.com/documentation/cdl/en/lrdict/63026/HTML/default/viewer.htm#a000199624.htm

One of your dates to be read is: 23/11/2010 9:45:34 and this is not a format that DATETIME will read. Your date should be
23NOV2010 9:45:34 in order for the DATETIME informat to work.

You will need to look for another INFORMAT (such as ANYDTDTM.) :
http://support.sas.com/documentation/cdl/en/lrdict/63026/HTML/default/viewer.htm#a002605552.htm

Also, for a FORMAT statement to work, you must list the variable to be formatted in the FORMAT statement. So this is incorrect:
[pre]
format datetime20.;
[/pre]

If you are reading variable X, and if X is read with the correct INFORMAT, then the appropriate FORMAT statement would be:
[pre]
format X datetime20.;
[/pre]

cynthia

[arpit]

Dear Cyntha,

But i got this date format from exel.i.e in DD/MM/YY HH:MM:SS

How i convert this into sas date format.

Thanks a lot for ur kind cooperation

Regards,
Arpit

[art297]

Arpit,

I think that the method described in the following tip might be exactly what you are looking for: http://www.sascommunity.org/wiki/Tip_of_the_Day:August_27

If one has date/time data that includes a space between the date and time, you can read it directly with the anydtdtm informat as long as you put an ampersand, after the variable in the input statement. The tip shows example code.

HTH,
Art

[arpit]

Dear Art,


options datestyle=dmy;

data have;
informat datetime anydtdtm18.;
format date date9.;
format time time8.;
input datetime &;
date=datepart(datetime);
time=timepart(datetime);
cards;
14/9/2007 11:25:59
12/7/2010 15:25:59
;


This code generate below output

datetime date time
1505388359 14SEP2007 11:25:59
1594567559 12JUL2010 15:25:59

it show 3 column
but I want in below mention format


datetime
14/09/2007 11:25:59
12/06/2010 15:25:59


Thanks for ur kind information.

Regards,
ARPIT

[Cynthia_sas]

Hi:
Once you have read your variable successfully, the number is stored internally as the number of SECONDS since January 1, 1960. so, as you can see from your "separated" DATE and TIME variables, you have successfully extracted the values from the internally stored number.

The way to control the DISPLAY of a variable (especially a variable whose value represents a date, a time or a date/time) is through the use of a FORMAT generally a statement (but for some procedures it is the FORMAT option). You have applied a FORMAT to the DATE variable (DATE9.) and to the TIME variable (time8.) in the following statements:
[pre]
format date date9.;
format time time8.;
[/pre]

But just because you used an INFORMAT to read the value of the DATETIME variable, a FORMAT will not automatically be applied. An INFORMAT only controls how a variable is read INTO SAS. A FORMAT controls how a variable is DISPLAYED, such as with PROC PRINT or any reporting procedure.

So, you will need to apply a FORMAT to the DATETIME variable, such as:
[pre]
format date date9. time time8. datetime datetime20.;
OR
format date ddmmyy10. time time8. datetime E8601DT20.;
[/pre]

To find out the available list of formats to use for your DATETIME variable, you can search the documentation for the topic "Functions by Category". I show two possible formats above. If the available pre-defined formats are not adequate to your needs, then PROC FORMAT allows the use of "date-time directives" in order to give you the opportunity to format date and time and date/time variables in almost any fashion you want.

The documentation on the use of date-time directives with PROC FORMAT is here:
http://support.sas.com/documentation/cdl/en/proc/61895/HTML/default/viewer.htm#a002473467.htm (looks specifically at using DATATYPE= and how to specify values for the PICTURE statement.)

and some other useful information is here:
http://support.sas.com/kb/24/621.html
http://www2.sas.com/proceedings/sugi31/243-31.pdf (picture formats for date/time variables starts on page 14 -- but the rest of the paper is worth reading, too.)

cynthia

[Cynthia_sas]

Hi:
Just because Excel gives the date in that format does not automatically mean that the DATETIME informat in SAS will read it. If your date does indeed look like
DD/MM/YY HH:MM:SS, then as suggested (twice now) you will need to use another informat to read the value.

The ANYDTDTM informat should work for you and you have a link to the documentation in my post and a link to a tip in another posting that should help you figure out what to do.

cynthia

[arpit]

I could not get it

Please help me out

[SPR]

Hello Arpit,

I did not find the exact format you requested. I see 2 possible solutions:
1) you agree to use DateTime18. format for the datetime variable or
2) it is possible to make a character variable dt in the requested format.

See code below:
[pre]
data have;
length dt $20;
informat datetime anydtdtm18.;
format date MMDDYY10.;
format time time8.;
input datetime &;
date=datepart(datetime);
time=timepart(datetime);
dt=put(date,MMDDYY10.)||" "||put(time,time8.);
format datetime Datetime18.;
cards;
14/9/2007 11:25:59
12/7/2010 15:25:59
;
run;
[/pre]
Sincerely,
SPR

[arpit]

it work properly

Thanks a lot SPR for your kind information.

Regards,
ARPIT

[art297]

If I correctly understand, you were able to read the data, but now need a format for outputting it the same way?

While not just roll your own format? I can't test the following at the moment, but it should work:

proc format;
picture mydate other='%0d/%0m/%0Y %0H:%0M:%0S' (datatype=datetime);
run;

data want;
set have;
format datetime mydate.;
run;

Art

[SPR]

Hello ArtC,

It perfectly works!

SPR

[art297]

Glad to hear that, but I'm not ArtC. I'm ArtT

[SPR]

Sorry ArtT,

It was my mistake.

SPR

[art297]

Absolutely no need to be sorry. You made my day!