PROC IMPORT Unable to read empty cells in a timestamp column

[SyidaRox]

Hello all - I am trying to use PROC IMPORT to read data from an EXCEL file, code below.  The columns that are giving me trouble are timestamps that are formatted like this (12/11/2018 4:02:39 PM) in the excel spreadsheet.  The issue is that if the first row has an empty cell then the format of the data changes.  If the first row has a timestamp then the data comes in formatted like this (09JUN2020 12:13:14), if the first row is blank then the rows that have timestamps come in formatted like this (06/09/2020 12:13:14).  I have tried to force the data type by using the DBDSOPT option and I used DATETIMEw, ANYDTDTTMw and CHAR(w) and none worked.  Any help is appreciated.

PROC IMPORT OUT=&outfile.
DATAFILE= &infile. 
DBMS=EXCELCS REPLACE;
RANGE="&sheetname.$";
DBDSOPTS= " DBTYPE=('UNDER_REVIEW_TS'='DATETIME19.' 'final_status_ts'='DATETIME19.' ) ";
TEXTSIZE=32767;
SCANTEXT=YES;
USEDATE=YES;
SCANTIME=YES;
SERVER="server_name";
PORT=1235;
SERVERUSER=&user_id.;
SERVERPASS=&pass_word.; 
RUN;

[ballardw]

Welcome to the ugly world of Excel and no settings of datatype.

Proc import uses a limited number of rows to set properties when reading from Excel. If any of the values in the first rows are blank the rules tell SAS to treat the column as character.

One way to avoid this is to 1) Save the file from Excel to CSV using the File Save As menu. and 2) use import to read the CSV file and add the GUESSINGROWS=MAX; statement to the proc import code. That way more rows will be examined before setting properties.

If you are going to read multiple files of the same supposed content, which sounds likely, then copy the data step code that appears in the log to editor, clean up the code by removing the line numbers and consider the lengths of the character variables. You may want to make them 15 to 25 percent longer unless you have a document that indicates what the maximum length of each variable should be..

SAVE the code and you can read similar structured data by saving them to csv, changing the Infile statement to point to the new file and change the output data set name if desired.

[Tom · Accepted solution]

You don't seem to using the right option there.  DBTYPE= is for setting the type to use when creating a variable in the remote database.  So you would use that with PROC EXPORT not PROC IMPORT.  DBSASTYPE= is for telling it what type to use when creating the variable in the SAS dataset.

You also do not seem to be setting the type properly. It wants you to tell it the type, not a SAS format or informat name. For DATETIME values just use DATETIME.  If you want to change how the values are displayed then you can attach a different format to the variable later.  If there is not a format that displays datetime values the way you like then roll your own with PROC FORMAT.

DBSASTYPE=(column-name-1=<'> SAS-data-type<'>
<…column-name-n=<'>SAS-data-type<'>> )

Syntax Description

column-name

specifies a DBMS column name.

SAS-data-type

specifies a SAS data type, which can be CHAR(n), NUMERIC, DATETIME, DATE, TIME. See the DBMS-specific reference section for your SAS/ACCESS interface for details.