Read data from excel into SAS

[Sasfan]

I am reading an excel file with multiple tabs. I am using SAS 9.4 Following is the approach that I am using.
Libname xllib xlsx “path”;

All the tabs contain a column Last_date. According to the excel file , if Last_date is blank,it means the record is active else the record is closed.

Now the issue is:if all the records are active, then last_date is blank.As a result when I use the above stmt to import, if one sheet has all active records ,it is imported as character. If other sheet of same spreadsheet has dates in Last_date,it is imported as date.
Now I am supposed to replace blank with a default date 9000/01/01 . Because I am using libname statement ,it is dynamic,meaning I wouldn’t know what tab will be imported as character or what tab will be imported as date.
I needed an opinion here;
1. Should I be writing the logic to handle this situation in a SAS code.
2. Should this situation be handled in the raw data file itself. Like,Last_date should be populated with default date in raw file itself.

The pros of the second method is all the tabs will be imported as date, which means ,the SAS code would be simpler and there will be less testing efforts.

PS :the raw data file here will be manually edited by some user.

[RichardDeVen]

Never rely on a user to do things correctly.  Perform the tests and checks in SAS, even if you have something in place in Excel to reduce the rate of incorrect or undesired data.

[Sasfan]

Thank you Richard for the valuable information . Appreciate it.

[DavePrinsloo]

As an aside, there are also issues when importing from Excel because it does not have fixed character column lengths. SAS samples each sheet to guess the length and type of columns. If you use the same code to import from Excel on a regular basis, and you want to append the import to the previous imports, then you need to give some thought to the maximum column lengths over time.

An approach that I take is to import the data into a temporary sas table and then convert the data to a structure that you want.
I generally use a much more generic macro that converts all the columns to fit the template structure, but for the sake of simplicity, I have some (untested) code here.
I use a macro ut_varlist that I posted previously for which you can download the code, place it in an autocall macro library (or %include before you start

(Code for ut_varlist is in an attachment to  https://communities.sas.com/t5/SAS-Programming/Macro-function-to-return-a-list-of-variables-in-a-tab...

Note that the code renames the column with the incorrect attributes, so that it does not clash with the column that you want to create.    I am taking your word that the last_date column is blank and can there´fore by simply dropped.   In my more generic macro (not here) I may have data that is imported as a text string but actually contains information that can be converted into a date.

/* set flag if last_date was imported as blank character */
%let date_is_char = %length(%ut_varlist(table=xlsx_temp_import, select=last_date, type=char) ne 0);

data final ;
set xlsx_temp_import
%if &date_is_char. = 1 %then %do;   
       /* rename the offending column */
      (rename = (last_date=_last_date));
      drop _last_date;
       /* create the desired column */
      format last_date date9.;
      /* avoid warning */
      call missing (last_date) 
  %end;
    ;
run;

[Sasfan]

Hello DavePrinsloo,
Thank you for letting me know your views.I will definable go through the routine you provided.