topic Re: Proc Import From Excel Table in SAS Programming

Proc Import From Excel Table

GeorgeBonanza — Tue, 05 Dec 2023 18:48:10 GMT

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;

Re: Proc Import From Excel Table

A_Kh — Tue, 05 Dec 2023 19:07:24 GMT

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;

Re: Proc Import From Excel Table

GeorgeBonanza — Tue, 05 Dec 2023 19:56:42 GMT

I appreciate you taking the time to respond but this isn't really the crux of my question. Perhaps I should have phrased the question differently. Using Proc Import, is there a way to refer to a table in Excel the same way you would refer to a sheet using "sheet=" or a named range using "range="? Essentially, is there something equivalent to "table="?

Re: Proc Import From Excel Table

Tom — Tue, 05 Dec 2023 20:07:20 GMT

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.

Re: Proc Import From Excel Table

GeorgeBonanza — Tue, 05 Dec 2023 20:26:48 GMT

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.

Re: Proc Import From Excel Table

Tom — Tue, 05 Dec 2023 20:55:48 GMT

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

Re: Proc Import From Excel Table

GeorgeBonanza — Tue, 05 Dec 2023 21:27:47 GMT

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=; ?

Re: Proc Import From Excel Table

Tom — Tue, 05 Dec 2023 23:31:46 GMT

@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.