You don't give us enough information so below is based on some assumptions. If I understand your problem then you're using Proc Import and in cases where all rows for the datetime column in your Excel source are missing you end up with a character variable. As to my knowledge there is no way to tell Proc Import to apply a specific variable type for a source column. It's based on scanning the source data - and if it's all missing then Proc Import defaults to a character variable.
Excel is just a bad source for data integration and should be avoided. But I know that it's sometimes not possible.
If the Excel source is rectangular then you could try and register it as metadata table object. I've done this in the past with mixed results. SAS(R) Data Integration Studio 4.4: User's Guide
Another way which I've already used is to first read the Excel source using Proc Import, then write the resulting table as text delimited file to disk and then define a External File metadata object for this csv. After that it's "out-of-the-box" ETL.
Such an approach adds some I/0 but it let's you much better control how you read the data for further processing.
In the past my thinking for such an approach was also that may be somewhere in the future I will be getting a "reasonable" extracts like a csv. So what I've done is to implement a separate DI job which only reads the Excel and writes it to disk as csv. The next job then reads this csv using a External File Metadata object. And if once in the future data is directly provided as csv one would only need to turn of the first conversion job.
Below a code example which uses such an approach. The csv gets written to a temporary file and no external file metadata object is used. You might decide to modify the code and split it up into 2 jobs using above approach.
/* create target table */
data target;
attrib charvar length=$20 datetimevar length=8 informat=datetime. format=datetime16. numvar length=8;
charvar='target';
datetimevar=datetime();
numvar=1;
run;
/* create table as it could look like after Proc Import with all values in datetime column missing */
data after_import_from_excel;
attrib charvar length=$20 datetimevar length=$10 numvar length=8;
charvar='source,test';
call missing(datetimevar);
numvar=2;
output;
output;
run;
/* create temporary file */
filename csvtmp temp;
/* write imported data to temporary file as csv */
ods csv file=csvtmp;
proc print data=after_import_from_excel noobs;
run;
ods csv close;
/* import csv with variable types as required for loading into target */
data source;
attrib charvar length=$20 datetimevar length=8 informat=datetime. format=datetime16. numvar length=8;
infile csvtmp dsd dlm="," firstobs=2 truncover;
input charvar datetimevar numvar;
run;
/* release temporary file */
filename csvtmp clear;
/* load source into target */
proc append base=target data=source force nowarn;
run;
