Reading an Excel data into SAS with using format DATE and format TIME

[sstoline]

Dear All:

I am trying to read the excel file "datatest". The data in that file look like the data below. The following is the SAS code I used, but I got an error message. I want the Record_Date and the Record_Time look in SAS the same as in Excel. That is look like the data below. I am not sure what I am doing wrong. When I copy paste these data in SAS Editor in the datalines statement, it works. But I have a large data set that I cannot use datlines in SAS Editor. I tried to use the proc import, but I do not know how to use format DATE and format TIME within it.

Any helps will be highly appreciated.

many thanks in advance

Steven

[email protected]

data datatest;

  infile 'C:\datatest.xlsx';

  input PAT_NAME $    RECORD_DATE :MMDDYY8.    RECORD_TIME :time5.;

format RECORD_DATE MMDDYY8.;

format RECORD_TIME time5.;

run;

These data was saved in Excel file named "datatest.xlsx".

==================================================

PAT_NAME	RECORD_DATE	RECORD_TIME
ALDEN,PAUL	06/04/2013	8:50
ALDEN,PAUL	06/04/2013	15:00
MAO,SUNNY B	06/07/2013	10:00
MAO,SUNNY B	06/07/2013	10:18
MAO,SUNNY B	06/07/2013	10:30
MAO,SUNNY B	06/07/2013	10:45
MAO,SUNNY B	06/07/2013	10:58
MAO,SUNNY B	06/07/2013	11:05
MAO,SUNNY B	06/07/2013	11:16
MAO,SUNNY B	06/07/2013	11:24
MAO,SUNNY B	06/07/2013	11:44
MAO,SUNNY B	06/07/2013	12:00
MAO,SUNNY B	06/07/2013	12:14
MAO,SUNNY B	06/07/2013	12:31
MAO,SUNNY B	06/07/2013	13:00

[Patrick]

An XLSX file is not a simple text file. Is your "RECORD_DATE" in the Excel spreadsheet a text field or a date field? If it's a date field then SAS should get it right once you're using the correct code to import such a spread-sheet.

There is a lot of information out there in public of how to read excel files. Try below links as a start.

http://blogs.sas.com/content/sasdummy/2011/05/18/behind-the-scenes-importing-excel-files-using-sas-e...

http://blogs.sas.com/content/sasdummy/2012/02/11/export-excel-methods/