Dear SAS community,
According to :
15778 - How to specify formats when importing data from Excel (sas.com)
proc import should allow specifying formats for imports.
However the statement returns an error:
PROC IMPORT OUT= WORK.relabel
DATAFILE= "&path\Variables and Labels(13NOV2023).xlsx"
DBMS=EXCEL REPLACE;
GETNAMES=YES;
FORMAT Member $15.Num 10. NewNum 10. Variable $40. Type $5. Len 5. Format $20. Informat $20. Label $40. NewLabel $40. Del 1. SortNo1 1.;
RUN;
37 PROC IMPORT OUT= WORK.relabel
38 DATAFILE= "&path\Variables and Labels(13NOV2023).xlsx"
SYMBOLGEN: Macro variable PATH resolves to XXX
39 DBMS=EXCEL REPLACE;
40 GETNAMES=YES;
41 FORMAT Member $15.Num 10. NewNum 10. Variable $40. Type $5. Len 5. Format $20. Informat $20. Label $40. NewLabel $40. Del 1. SortNo1 1.;
42 RUN;
WARNING: FORMAT statement is not supported and will be ignored in this procedure. Use PROC DATASETS to modify the data set instead.
SYMBOLGEN: Macro variable _IMEXSERROR_ resolves to SERROR
NOTE: WORK.RELABEL data set was successfully created.
NOTE: The data set WORK.RELABEL has 3459 observations and 12 variables.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 0.74 seconds
cpu time 0.29 seconds
I would like to implement specification sheets for database extracts. The database extracts are .sas7bdat files. The excel sheets should help users what columns they want to have visualized in the final excel workbook. Converting the .xlsx files to .csv is not an option in this case (like in: Solved: PROC IMPORT XLSX File and Specify Formats - SAS Support Communities).
Thanks in advance for assistance and best regards
Lukas
Hi Kurt,
thanks for pointing that out.
Since R (Cell and Column Types • readxl (tidyverse.org))
library(readxl) read_excel("yo.xlsx", col_types = c("date", "skip", "guess", "numeric"))
or Python (Python pandas: how to specify data types when reading an Excel file? - Stack Overflow)
import pandas as pd df = pd.read_excel('Book1.xlsx',sheetname='Sheet1',header=0,converters={'names':str,'ages':str})
are both capable of handling excel data for professional purposes, I will prefer to use those for the required task.
Thanks and BR
Lukas
I've never seen this syntax from the usage note you reference https://support.sas.com/kb/15/778.html
It's also a weird note because the table indicates it's a bug without a fix but the code looks like it should be a working solution ....and that for the very old version SAS 9.1.3
It's clearly not in the documentation: https://go.documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/acpcref/n0msy4hy1so0ren1acm90iijxn8j.htm
Suggest you use syntax as suggested by the warning text.
WARNING: FORMAT statement is not supported and will be ignored in this procedure. Use PROC DATASETS to modify the data set instead.
Proc Datasets changes the descriptor portion of your table in-place. This process will take almost no time and is also independent of the data volume in the table.
I've made the assumption that the format statement in your code contains key/value pairs of variable name and related format - so some variables are called Format or Label.
If that's not the case but you want to define formats, labels etc. for specific variables then the syntax needs to change. It's all documented here.
PROC IMPORT OUT= WORK.relabel
DATAFILE= "&path\Variables and Labels(13NOV2023).xlsx"
DBMS=EXCEL
REPLACE;
GETNAMES=YES;
RUN;
proc datasets lib=work nolist;
modify relabel;
FORMAT
Member $15.
Num 10.
NewNum 10.
Variable $40.
Type $5.
Len 5.
Format $20.
Informat $20.
Label $40.
NewLabel $40.
Del 1.
SortNo1 1.
;
run;
quit;
Hi Patrick,
thanks for your reply. Yes it is indeed interesting how inconsistent the documentation of SAS is.
Your approach did not help unfortunately.
ERROR: You are trying to use the numeric format F with the character variable NewNum in data set WORK.RELABEL.
This is the error from your suggested proc datasets procedure.
As asked in my initial question the excel file should act as a configuration file for non-programmers. That means, that a variable such as Del should be initialized as numeric, no matter what is written in the column.
Is this possible in SAS?
Dear Kurt,
thanks for your help.
Unfortunately this is exactly what I tried to avoid (see my initial post).
BR
Lukas
The format-statement assigns formats to the variables written to the dataset, it has no impact on how values are read from a file. So even if the format statement could be used in proc format, it won't fix the problem of types and lengths being guessed by proc import. The only way to avoid such problems is using text files and writing a data step to read the file.
In order to have solid, consistent results which are usable in further automation, text files and DATA steps are the way to go. IMPORT is a stopgap, usable for a quick shot, but not for professional purposes.
Since all SAS methods to read Excel files are making guesses based on the current contents, the same holds true. Can be used for a quick shot, but not for professional work.
Hi Kurt,
thanks for pointing that out.
Since R (Cell and Column Types • readxl (tidyverse.org))
library(readxl) read_excel("yo.xlsx", col_types = c("date", "skip", "guess", "numeric"))
or Python (Python pandas: how to specify data types when reading an Excel file? - Stack Overflow)
import pandas as pd df = pd.read_excel('Book1.xlsx',sheetname='Sheet1',header=0,converters={'names':str,'ages':str})
are both capable of handling excel data for professional purposes, I will prefer to use those for the required task.
Thanks and BR
Lukas
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
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.
Ready to level-up your skills? Choose your own adventure.