When using the XLSX option to read Excel data, I would like to be able to force an Excel column to be read into a character variable, even if the column contains only numeric values. With other engines, I believe this would be done with the DBSASTYPE option.
Sample code:
libname myfile xlsx "%sysfunc(pathname(work))\myexcel.xlsx" ;
*write sashelp.class to excel ;
data myfile.class ;
set sashelp.class ;
run ;
*Failing pseudo code to read in the Excel file, forcing Age to be character ;
data ClassCharAge ;
set myfile.class(dbsastype=(Age='CHAR(20)')) ;
run ;
Use case is that suppose I receive an incremental Excel file weekly, and I have a SAS job to load the data into a database. I have a variable that is defined as character in database, but some incremental Excel files may only have numeric values for that variable. When I import such an Excel file, it is loaded into a numeric variable in SAS even though the database and my SAS code expect it to be character.
Yes I can add code to by ETL job to check if the field is numeric or character and then convert it from numeric to character myself, and yes I hate Excel and could avoid this by converting the Excel file to CSV or just telling the customer not to send me Excel in the first place. But since many engines support the DBSASTYPE option, would be nice to see it supported by the XLSX engine.
Related thread:
https://communities.sas.com/t5/Base-SAS-Programming/Reading-XLSX-file-and-force-SAS-column-types/m-p...
