Help using Base SAS procedures

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

Reply
Contributor
Posts: 32

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

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

sstoline@gmail.com

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_NAMERECORD_DATERECORD_TIME
ALDEN,PAUL06/04/20138:50
ALDEN,PAUL06/04/201315:00
MAO,SUNNY B06/07/201310:00
MAO,SUNNY B06/07/201310:18
MAO,SUNNY B06/07/201310:30
MAO,SUNNY B06/07/201310:45
MAO,SUNNY B06/07/201310:58
MAO,SUNNY B06/07/201311:05
MAO,SUNNY B06/07/201311:16
MAO,SUNNY B06/07/201311:24
MAO,SUNNY B06/07/201311:44
MAO,SUNNY B06/07/201312:00
MAO,SUNNY B06/07/201312:14
MAO,SUNNY B06/07/201312:31
MAO,SUNNY B06/07/201313:00
Respected Advisor
Posts: 3,887

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

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/

Ask a Question
Discussion stats
  • 1 reply
  • 271 views
  • 0 likes
  • 2 in conversation