Dear expert,
Recently, when I tried to import an EXCEL file into SAS dataset, I found that the variable names in the output dataset were not displayed correctly. Could any expert please correct my concepts ?
Problem :
An EXCEL 2003 file with variable names, namely NAME, EXAM & SCORE in columns A, B and C. A new column showing the average total was also computed in column D.
EXCEL 2003 (sheet="Name" in NAME.xls)
-------------------------------------
Col A Col B Col C Col D
name exam score 69.5
David 1 60
David 2 5
David 3 75
Kitty 1 70
Kitty 2 85
Mary 1 25
Mary 2 90
Mary 3 85
Mary 4 100
Mary 5 100
When I submitted the PROC IMPORT below, the output dataset variable names were A, B, C and D (blank column) even though PROC IMPORT OUT=work.tt99 (DROP=D) or GETNAMES=YES were both added in the syntax. However, when I deleted the column D in the EXCEL file, the variable names were displayed correctly.
proc import out = work.tt99
datafile = "d:\name.xls"
DBMS = xls replace;
sheet = "name";
getnames = YES;
run;
or
proc import out = work.tt99 (drop=D)
datafile = "d:\name.xls"
DBMS = xls replace;
sheet = "name";
getnames = YES;
run;
Hi,
Simple really. SAS has specific rules regarding naming convention of variables. In your column D in the Excel grid you have the Number 69.5, this is no way a valid column name in SAS hence the import procedure removes the getnames statement and reads the data in from row 2, with default column names. If you want to import data like that then you would need to save as CSV and write the import datastep yourself, or move that number down a row.
You can use RANGE to exclude the last column .
proc import out = work.tt99
datafile = "c:\temp\name.xls"
DBMS = xls replace;
range = "name$A1:C11";
getnames = YES;
run;
Xia Keshan
Thank you all of you.
Actually, the excel file may contain data in columns A-F and we don't know the total number of data rows before. Could I write this ?
proc import out = work.tt99
datafile = "c:\temp\name.xls"
DBMS = xls replace;
range = "name$A:F";
getnames = YES;
run;
regards
May Wong
You could, but then you would end up with a dataset with 256 columns. Really, if you are importing data from third party you need to have an agreement of what data would be sent, in what format, and agreed on both sides. Otherwise you will run into trouble each time you import a File. For instance this run I call the sheet xyz, next time I call it yzx. Sometimes I put only number data in X, next time I put NA etc. Excel is not a tool for the transfer of data, hence it is doubly important to agree up front what the structure (as that is the main is) is.
why not write a very large number for row ?
"name$A1:F32456";
Wow , I can't believe my eye , you can use
"name$A:F";
Message was edited by: xia keshan
It's great. Thanks all of you.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.