BookmarkSubscribeRSS Feed
Calcite | Level 5

Hi everyone!

I am very very new to SAS and to any statistical analysis to be honest, so I am a little be lost.

I have some data in an Excel file, that I uploaded to SAS. The problem is that the informat of many variables is not correct (some are readed as characters where they should be numeric, and vice versa). I can't seem to find which code to use to correct the informat of my variable with my imported file.

Here is the code that I have for now :


*Creation of a library;

LIBNAME memoire "/home/u63169456/Essai2";

*Importation of file;

PROC IMPORT DATAFILE = "/home/u63169456/Essai2/Copy of Données 19-02.xlsx"
DBMS = xlsx
OUT = memoire;


What code am I missing? :') I've been trying to figure it out on google, youtube, sas documentation and various books for the last couple of days. Please save me lol


Thank you very much!

Super User Tom
Super User

You don't have any control over how to import from a spreadsheet, other than which part of the spreadsheet SAS should read. SAS will determine the variable type from the types of the CELLS in that COLUMN in the spreadsheet.  If ANY of the cells have characters strings then the variable is character.  If ALL of the cells are empty then the variable is defined as character with a length of 1 byte (so it uses the minimum space possible).


So either FIX the spreadsheet so that all of the cells in the column have the right type of values.


Or perhaps save the spreadsheet as a delimited text file, such as a CSV file, and read the CSV file using your own data step (not PROC IMPORT) and you will have complete control over how the variables are defined and read and what formats (if any) you want to permanently attach to them.

Calcite | Level 5
Thank you!
Obsidian | Level 7

You can try to use a LIBNAME for XLSX files if you have SAS 9.4M2 and larger maintenance(M).
 You can use the LIBNAME to directly import from spread sheet or export to
spread sheets. LIBNAMES are great.

See some of the SAS LIBNAME XLSX You Tube Videos to get a new look at the feature. These
YouTube videos were created 3 years ago by Kathy Kiraly


Jonas V. Bilenas


Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.


Register now!

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 3 replies
  • 1 like
  • 3 in conversation