BookmarkSubscribeRSS Feed
arpit
Calcite | Level 5
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 .
15 REPLIES 15
Cynthia_sas
SAS Super FREQ
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
Calcite | Level 5
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
Opal | Level 21
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
Calcite | Level 5
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
SAS Super FREQ
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
SAS Super FREQ
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
Calcite | Level 5
I could not get it

Please help me out
SPR
Quartz | Level 8 SPR
Quartz | Level 8
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
Calcite | Level 5
it work properly

Thanks a lot SPR for your kind information.

Regards,
ARPIT
art297
Opal | Level 21
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
Quartz | Level 8 SPR
Quartz | Level 8
Hello ArtC,

It perfectly works!

SPR
art297
Opal | Level 21
Glad to hear that, but I'm not ArtC. I'm ArtT
SPR
Quartz | Level 8 SPR
Quartz | Level 8
Sorry ArtT,

It was my mistake.

SPR
art297
Opal | Level 21
Absolutely no need to be sorry. You made my day!

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 Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 15 replies
  • 2985 views
  • 0 likes
  • 5 in conversation