topic Re: import excel by defining type of variables in SAS Programming

import excel by defining type of variables

AlexeyS — Fri, 14 Jan 2022 14:16:21 GMT

Hi,

To read excel file i usually use proc import. But in this case, i want to define type of variables and length during the reading of excel file. Of course proc import cannot do that. Maybe there are another way or procedure i can do that.

Thank you

Re: import excel by defining type of variables

ChrisHemedinger — Fri, 14 Jan 2022 14:21:55 GMT

When reading Excel files with PROC IMPORT or LIBNAME XLSX, the data types and lengths are taken from the cues SAS reads from the Excel file. If you want more control over the final output, it usually requires a second step (DATA step, PROC SQL, or PROC DATASETS) to change/transform the way variables are stored and their metadata.

Others on this forum will (rightly) say that reading CSV gives you much more flexibility on the input process. But...we are given what we are given and if an Excel file is the input, it can be a little work to add the rigorous structures we require for downstream analysis and reporting in SAS.

Re: import excel by defining type of variables

AllanBowe — Fri, 14 Jan 2022 14:27:24 GMT

The approach taken in Data Controller is to take the variable types (numeric, character, dates, datetimes, times, precision, lengths, etc) from the target table that you are trying to load. Excel is parsed in the browser (using an OEM version of sheetjs) and then - after validations - sent to SAS.

A very basic / simple example of this can be found in the following macro, which could surely be adapted for excel: https://core.sasjs.io/mp__csv2ds_8sas.html

The idea of reading the types from Excel sounds nice in theory but is very shaky in practice. A column could contain all numerics, but in fact be a character field (eg with leading zeros).

A column could contain single alphabetical characters ($1) but in fact be fully populated with special SAS missing numerics.

A column could be empty, in which case is it character or numeric?

Far better to have a template in mind, where possible...

Re: import excel by defining type of variables

ChrisHemedinger — Fri, 14 Jan 2022 14:33:14 GMT

Since Allan chimed in about how DataController does it, I'll add that SAS Enterprise Guide takes a similar approach. The Import Data task in SAS Enterprise Guide will read the Excel file metadata and present you with all of the options to select columns you want, types, formats, lengths, etc. Then the task will read the Excel file and convert to text, sending to SAS to import like a CSV.

If you are okay with a client-side process that reads the Excel file first and sends it to SAS, then SAS Enterprise Guide or a tool like DataController can serve. However, many people need a process that can run 100% repeatable in SAS code.