I have a file in Excel that I use to import the data into SAS.The format of the data in the Excel is this:
28-05-2014 10:21:20
27-05-2014 14:22:26
27-05-2014 11:36:33
27-05-2014 17:14:55
27-05-2014 10:34:56
27-05-2014 19:03:48
27-05-2014 14:30:56In SAS, the type is text, length is 19 and format is $19.I am using this column to admission date which looks like this 08JUL2013:08:22:00. Therefore I would like to convert the above datetime to this format 28MAY2014:10:21:20 or 28MAY2014:10:21:00Please let me know what I should do?THank you
Hey thank you the one by user24feb works.
Ideally you should fix it in the import step, perhaps by using usedate=yes and mixed=yes in your proc import step.
If not then you can convert it after importing it by using something like the following:
new_date=input(date, anydtdtm.)
data date_formats;
input date $50.;
cards;
28-05-2014:10:21:20
27-05-2014:14:22:26
27-05-2014:11:36:33
27-05-2014:17:14:55
27-05-2014:10:34:56
27-05-2014:19:03:48
27-05-2014:14:30:56
;
run;
data out;
set date_formats;
new_date=input(date,anydtdtm.); /*converting character to date format */
format new_date datetime18.; /* Printing the data into wanted format */
run;
And if you want a more specific informat to handle that timestamp layout, try
FINDFDT19.
For a more general search method (than asking onlin) have a look at paper
http://support.sas.com/resources/papers/proceedings14/1744-2014.pdf
Titled VFORMAT lets SAS(R) Do the Format Searching
where this informat is one of the results of a search
Hi Thank you. I tried your's but the day and the month got switched.
another try
(can't see whether you have 2 variables or 1, but) use DHMS:
Data A;
Input Text_DT $19.;
Datalines;
28-05-2014 10:21:20
27-05-2014 14:22:26
27-05-2014 11:36:33
27-05-2014 17:14:55
27-05-2014 10:34:56
27-05-2014 19:03:48
27-05-2014 14:30:56
;
Run;
Data B;
Set A;
DT=DHMS(Input(Scan(Text_Dt,1,' '),DDMMYY10.),Scan(Scan(Text_DT,2,' '),1,':'),Scan(Scan(Text_DT,2,' '),2,':'),Scan(Scan(Text_DT,2,' '),3,':'));
Format DT DateTime19.;
Run;
Hey thank you the one by user24feb works.
Please mark it as answered, for future users.
Vortex
did you try the findfdt19. Informat?
Data direct;
input timestamp findfdt19. ;
put timestamp= datetime. ;
Cards;
27-05-2014 14:30:56
;
"Solution" in this thread does not work.
A less complex function also works. For future reference.
DATA XX(DROP = DATE DATEVARIABLE);
SET DSN;
DATE = SUBSTR(DATEVARIABLE,1,10);
NEW_DT = INPUT(DATE, ANYDTDTE11.);
FORMAT NEW_DT MMDDYY8.;
RUN;
Hi,
I am using your code too. I have to read 2 dates and they are of the form 2015-10-01.
The first column works perfectly .However, the second column does not work and I am using the same code for both. Please help
data C;
input text_dt $19. text_dte $20.;
datalines;
2015-10-06 09:53:25 2015-10-07 07:51:01
;
run;
data C;
format actual_start actual_end datetime20.;
set C;
actual_start =dhms(input(scan(text_dt,1,' '),yymmdd10.),scan(scan(text_dt,2,' '),1,':'),
scan(scan(text_dt,2,' '),2,':'),scan(scan(text_dt,2,' '),3,':'));
actual_end =dhms(input(scan(text_dte,1,' '),yymmdd10.),scan(scan(text_dte,2,' '),1,':'),
scan(scan(text_dte,2,' '),2,':'),scan(scan(text_dte,2,' '),3,':'));
run;
ERROR :-
Hi,
Sorry for the confusion. I fixed this using colum delimiters as follows:-
data input_data;
input text_dt $ 1-19 text_dte $ 21-40;
.
.
.
.
.
Rest of the code is same.
Thanks
Here's a neater solution.
num_datetime=input(text_datetime,anydtdtm23.); Format num_datetime DateTime23.3;
why did u used anydtdtm23. here?
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.