Is it possible to import an Excel Table into SAS using Proc Import? In Excel, the table has a name, in this case it's "TBL", and appears in the Name Manager. However, the range associated with "TBL" does not contain the header row which contains the variable names. I've tried using "range=" since this works with named ranges but it does not appear to work with tables. The table is in a sheet with other data, pivot tables, graphs, etc. so "sheet=" will not work. Any insight is appreciated. Thanks in advance.
proc import out= WANT
datafile= "C:\ExcelFile.xlsm"
dbms= EXCEL replace;
range= "TBL";
run;
So read it from the XML.
1 data test; 2 infile 'c:\downloads\tables.xlsx' zip member='xl/tables/table1.xml' recfm=n dsd dlm=' '; 3 input @ 'ref=' ref :$50.; 4 call symputx('ref',ref); 5 stop; 6 run; NOTE: The infile 'c:\downloads\tables.xlsx' is: (system-specific pathname), (system-specific file attributes) NOTE: 1 record was read from the infile (system-specific pathname). NOTE: The data set WORK.TEST has 0 observations and 1 variables. NOTE: DATA statement used (Total process time): real time 0.01 seconds cpu time 0.01 seconds 7 8 %put &=ref; REF=A2:C4
Use GETNAMES=YES statement to import variable names from the source data.
proc import out= WANT
datafile= "C:\ExcelFile.xls"
dbms= EXCEL replace;
getnames= yes;
run;
So they made a named range that does not include the header row?
You can use the actual cell names instead.
proc import
dbms=xlsx
file='myfile.xlsx'
out=want replace
;
range='mysheet$A12:B27';
run;
But unless you examine the individual XML files in the XLSX file (or in your case it looks like you have an XLSM file) it will be hard to programmatically determine where to find the name row.
It's something slightly different than a named range. It's a table (you create it by selecting your data in Excel and pressing ctrl + T). When you look at it in the Name Manager it has a "Name" and "Refers To" range just like a named range would but the "Refers To" cells only reference the body of the table without the headers. It's preferable to a named range in that the range expands automatically when new data is added. (there are ways to make named ranges dynamic but this allows other functionality as well). I suppose what I would need is something like "table=" instead of "range=" but I don't see that as an option. Thanks for taking the time to respond.
So read it from the XML.
1 data test; 2 infile 'c:\downloads\tables.xlsx' zip member='xl/tables/table1.xml' recfm=n dsd dlm=' '; 3 input @ 'ref=' ref :$50.; 4 call symputx('ref',ref); 5 stop; 6 run; NOTE: The infile 'c:\downloads\tables.xlsx' is: (system-specific pathname), (system-specific file attributes) NOTE: 1 record was read from the infile (system-specific pathname). NOTE: The data set WORK.TEST has 0 observations and 1 variables. NOTE: DATA statement used (Total process time): real time 0.01 seconds cpu time 0.01 seconds 7 8 %put &=ref; REF=A2:C4
Thanks so much. This is a very creative solution. Is this essentially what proc import is doing behind the scenes when importing a sheet or named range from Excel? If so, is there a way to modify the code so that it reads the XML from code run on a SAS server similar to how I'd modify a proc import by adding serveruser=; serverpass=; server=; port=; ?
@GeorgeBonanza wrote:
Thanks so much. This is a very creative solution. Is this essentially what proc import is doing behind the scenes when importing a sheet or named range from Excel? If so, is there a way to modify the code so that it reads the XML from code run on a SAS server similar to how I'd modify a proc import by adding serveruser=; serverpass=; server=; port=; ?
No.
You will need to have access to the file directly.
What you are talking about is using SAS PC FILES server to access the file. That does not even work with the XLSX engine. I think that only works using the Windows supplied tools that the EXCEL engine uses.
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.