BookmarkSubscribeRSS Feed
Firenzee
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
replace
OUT = memoire;
GETNAMES=yes;
run;

 

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!

3 REPLIES 3
Tom
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.

Firenzee
Calcite | Level 5
Thank you!
jbilenas
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

https://www.bing.com/videos/search?&q=sas+libname+xlsx&view=detail&mid=C4F78C565029C018D133C4F78C565...

 

Jonas V. Bilenas

sas-innovate-2024.png

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!

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
  • 323 views
  • 1 like
  • 3 in conversation