BookmarkSubscribeRSS Feed
Pyrite | Level 9


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

Community Manager

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.

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.
Barite | Level 11

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:


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...

SAS Challenges - SASensei
MacroCore library for app developers
SAS networking events (BeLux, Germany, UK&I)

Data Workflows, Data Contracts, Data Lineage, Drag & drop excel EUCs to SAS 9 & Viya - Data Controller
DevOps and AppDev on SAS 9 / Viya / Base SAS - SASjs
Community Manager

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.

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.


Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.


Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 3 in conversation