Importing table with unknown length from Excel .xlsm

0
votes

I want to transfer a table from Excel to SAS (version is 9.2 and Excel file format is .XLSM, macro). The column names will be read from the cell B3 and the data will start from the cell B4, like below:

  A   B   C   D   E   F   G ...
1
2
3   Col1 Col2 
4    15   20
5    16   21
6    ... ...

The problem is that the last row number is unknown, because the table length can be 200 rows today and it can be 350 rows tomorrow.

So how can I import this table from Excel (.XLSM) to SAS-table?

I read somewhere that we can use DATAROW in Proc Import when DBMS=EXCEL like below: 

proc import datafile = "!datafile" out=Table1 DBMS = EXCEL REPLACE; 
    SHEET = "Sheet1"; GETNAMES=YES; MIXED=YES; USEDATE=YES; SCANTIME=YES; NAMEROW=3; DATAROW=4;
run;

However, SAS cannot recognize the DATAROW option, giving the error:

ERROR 180-322: Statement is not valid or it is used out of proper order.

There is another way of importing table from Excel like:

PROC SQL;
  CONNECT TO EXCEL            (PATH='C:\\thepath\excelfile.xlsm');
  Create Table Table1 as SELECT * FROM CONNECTION TO EXCEL 
  (SELECT * FROM [Sheet1$]);
  DISCONNECT FROM EXCEL;
QUIT;

Does anyone know how to export a table with an unknown number of rows from .XLSM to SAS?

2
excelsasprocimport-from-excelproc-sql

2 Answers

0
votes

I found an "ineffective" alternative solution which reads all possible rows in Excel (reads 50.000 rows), at the same time it checks every row under the column Col1 if these rows have a value.

It takes 7-8 seconds, and it works. But as I wrote, it feels ineffective to read the whole 50.000 rows. Does anyone have any better idea?

PROC SQL;
   CONNECT TO EXCEL            (PATH='C:\\thepath\excelfile.xlsm');
   Create Table Table1 as SELECT * FROM CONNECTION TO EXCEL 
   (SELECT * FROM [Sheet1$B3:C50000] WHERE Col1 IS NOT NULL);
   DISCONNECT FROM EXCEL;
QUIT;
0
votes

You can use a direct connection to Excel using the libname statement:

libname xlsFile Excel 'C:\\thepath\excelfile.xlsm';

data want;
    set xlsFile.'Sheet1$'n(firstobs=3);
    where NOT missing(Col1);
run;

This is assuming you have Excel installed on the SAS server, and have purchased SAS/ACCESS to PC Files.