DATA Step, Macro, Functions and more

Import data from Excel file that contains rows with merged cells

Frequent Contributor
Posts: 103

Import data from Excel file that contains rows with merged cells

Does anyone know of a quick way to import data from an Excel file that contains rows of merged cells? Something like this:




Ideally, I would like the data in the merged cells to become a variable in the SAS dataset. And if possible, I would like to avoid SQL or macros.


Thanks in advance!

SAS 9.4 (TS1M4) X64_7PRO WIN 6.1.7601 Service Pack 1 Workstation

Super User
Posts: 23,726

Re: Import data from Excel file that contains rows with merged cells

There isn't very much control when importing from Excel.


I'd do a default import and then fix it in SAS.

If this is a one time job, I'd fix it in Excel. 

Valued Guide
Posts: 505

Re: Import data from Excel file that contains rows with merged cells

HAVE AN EXCEL SHEET and I need to input merged headers and merged data rows

* I think you need full SAS for this because
  only 'proc export' works in the reduced SAS enviroment.

This solution reads merged headers and merged row cells.
Merged headers within the data portion  that do not start in column 1
are an issue. You need to know the starting cell of
the interior merged header and read the ranges above and below
the interior merged header. You may be able to use
DBSASTYPE to change a interior column type to a long character and
avoid multiple range reads.


* create a sheet with two merged headers and one merged row og data;
%utlfkil(d:/xls/mrgcls.xlsx); * delete if exists;
ods excel file="d:/xls/mrgcls.xlsx" ;
ods excel options(sheet_name="mrgcls" sheet_interval="none");
proc report data=sashelp.class missing;
COLUMN ("This is a merged cell"  NAME AGE ("Lab values" HEIGHT WEIGHT));
define name   / group ;
break before name / skip;
compute before name ;
 if name='Carol' then len=10;
 else len=0;
 line txt $varying. len;
ods excel close;

* merged cells are tricky especially if they occur randomly;
* however if you know where they are you can extract them.

HAVE This is a merged cell Lab values NAME AGE HEIGHT WEIGHT Alfred 14 69 112.5 Alice 13 56.5 84 Barbara 13 65.3 98 MERGED Carol 14 62.8 102.5 Henry 14 63.5 102.5 James 12 57.3 83 Jane 12 59.8 84.5 Janet 15 62.5 112.5 */ WANT HEADER
GET Merged Headers Up to 40 obs WORK.HEADER total obs=2 Obs F1 F2 F3 F4 1 This is a merged cell 2 Lab values ... DATA Get Merged data Up to 40 obs WORK.DATA total obs=20 Obs NAME AGE HEIGHT WEIGHT 1 Alfred 14 69.0 112.5 2 Alice 13 56.5 84.0 3 Barbara 13 65.3 98.0 4 MERGED . . . 5 Carol 14 62.8 102.5 6 Henry 14 63.5 102.5 7 James 12 57.3 83.0 8 Jane 12 59.8 84.5 9 Janet 15 62.5 112.5 ... SOLUTION libname xls 'd:/xls/mrgcls.xlsx' header=no scantext=no; data header; set xls.'mrgcls$A1:E2'n; ;run;quit; libname close; libname xls 'd:/xls/mrgcls.xlsx'; data data; set xls.'mrgcls$A3:E9999'n; /* over specify */ ;run;quit; libname xls clear;


Ask a Question
Discussion stats
  • 2 replies
  • 3 in conversation