Proc IMPORT has the SHEET= statement to read data from a NamedRange or an explicit cell range reference.
What do you do when you are given a workbook with a Table in it, and you need to read its data into SAS?
The SHEET= statement will fail if you use the name of the Table
Open the workbook and copy the range into my SAS code? No way I'm doing that more than twice. The same workbook a week later and the table size could have changed and now my SAS code is not aligned with the table.
The functionality demonstrated here should be in the Proc IMPORT engine for DBMS=xlsx. Either a new statement TABLE= or update SHEET= to also handle tables.
Base SAS does have the tools to discover the table range and then Proc IMPORT from the discovered range.
Here is some code to read in the Table data that is in an Excel file.
%let workbook_file=c:\temp\blank.xlsx ;
dm 'clear log; clear output;' ;
options source ;
options linesize=200 ;
proc template;
%let font = Calibri ;
define style styles.pearl_custom;
parent = styles.pearl;
class fonts /
'TitleFont2' = ("&font",10pt,bold)
'TitleFont' = ("&font",11pt,bold)
'StrongFont' = ("&font",8pt,bold)
'EmphasisFont' = ("&font",8pt,italic)
'headingEmphasisFont' = ("&font",9pt,bold italic)
'headingFont' = ("&font",8pt,bold)
'docFont' = ("&font",8pt);
end;
run;
ods _all_ close ;
ods html path='c:\temp' file="excel-data-table.html" style=styles.pearl_custom ;
*----------------------------------------------------------------------;
* Find each table definition ;
*----------------------------------------------------------------------;
%macro halt () ; %abort cancel ; %mend ;
%macro noop () ; %mend ;
data _null_ ;
rc = filename('opentest',"&workbook_file") ;
if rc ne 0 then do ;
msg = sysmsg() ; put msg "!" ;
call execute('%nrstr(%halt;)') ;
end ;
fid = fopen ('opentest','i',0,'B') ;
if fid = 0 then do ;
msg = sysmsg() ; put msg ;
call execute('%nrstr(%halt;)') ;
end ;
else do ;
rc = fclose (fid) ;
put 'NOTE: Workbook file can be opened' ;
end ;
rc = filename('opentest') ;
run ;
*----------------------------------------------------------------------;
* Locate .xlsx items that specify data table range and related sheet ;
*----------------------------------------------------------------------;
filename wbkrel catalog 'WORK.SXLEMAPS.WBKREL.SOURCE' ;
filename wshtbl catalog 'WORK.SXLEMAPS.WSHTBL.SOURCE' ;
filename wshrel catalog 'WORK.SXLEMAPS.WSHREL.SOURCE' ;
filename wbkmap catalog 'WORK.SXLEMAPS.WORKBOOK.SOURCE' ;
filename tblref catalog 'WORK.SXLEMAPS.TBLREF.SOURCE' ;
filename null temp ;
data _null_ ;
infile datalines truncover ;
file wbkrel ; do until (0) ; input line $char200. ; if line =: '<!' then leave ; l=length(line) ; put line $varying. l ; end ;
file wshtbl ; do until (0) ; input line $char200. ; if line =: '<!' then leave ; l=length(line) ; put line $varying. l ; end ;
file wshrel ; do until (0) ; input line $char200. ; if line =: '<!' then leave ; l=length(line) ; put line $varying. l ; end ;
file wbkmap ; do until (0) ; input line $char200. ; if line =: '<!' then leave ; l=length(line) ; put line $varying. l ; end ;
file tblref ; do until (0) ; input line $char200. ; if line =: '<!' then leave ; l=length(line) ; put line $varying. l ; end ;
stop ;
datalines ;
<SXLEMAP version="2.1">
<TABLE name="WorkbookRelationships">
<TABLE-PATH>/Relationships/Relationship</TABLE-PATH>
<COLUMN name="Id">
<PATH>/Relationships/Relationship/@Id</PATH>
<TYPE>character</TYPE>
<DATATYPE>string</DATATYPE>
<LENGTH>32</LENGTH>
</COLUMN>
<COLUMN name="Target">
<PATH>/Relationships/Relationship/@Target</PATH>
<TYPE>character</TYPE>
<DATATYPE>string</DATATYPE>
<LENGTH>200</LENGTH>
</COLUMN>
</TABLE>
</SXLEMAP>
<!-- Next -->
<SXLEMAP version="2.1">
<TABLE name="SheetTableParts">
<TABLE-PATH>/worksheet/tableParts/tablePart</TABLE-PATH>
<COLUMN name="Id">
<PATH>/worksheet/tableParts/tablePart/@r:id</PATH>
<TYPE>character</TYPE>
<DATATYPE>string</DATATYPE>
<LENGTH>32</LENGTH>
</COLUMN>
</TABLE>
</SXLEMAP>
<!-- Next -->
<SXLEMAP version="2.1">
<TABLE name="WorksheetRelationships">
<TABLE-PATH>/Relationships/Relationship</TABLE-PATH>
<COLUMN name="Id">
<PATH>/Relationships/Relationship/@Id</PATH>
<TYPE>character</TYPE>
<DATATYPE>string</DATATYPE>
<LENGTH>32</LENGTH>
</COLUMN>
<COLUMN name="Target">
<PATH>/Relationships/Relationship/@Target</PATH>
<TYPE>character</TYPE>
<DATATYPE>string</DATATYPE>
<LENGTH>200</LENGTH>
</COLUMN>
</TABLE>
</SXLEMAP>
<!-- Next -->
<SXLEMAP version="2.1">
<TABLE name="sheets">
<TABLE-PATH>/workbook/sheets/sheet</TABLE-PATH>
<COLUMN name="name">
<TYPE>character</TYPE>
<LENGTH>32</LENGTH>
<PATH>/workbook/sheets/sheet/@name</PATH>
<DATATYPE>string</DATATYPE>
</COLUMN>
<COLUMN name="id">
<TYPE>character</TYPE>
<LENGTH>32</LENGTH>
<PATH>/workbook/sheets/sheet/@r:id</PATH>
<DESCRIPTION>http://schemas.openxmlformats.org/officeDocument/2006/relationships</DESCRIPTION>
<DATATYPE>string</DATATYPE>
</COLUMN>
</TABLE>
<TABLE name="definedNames">
<TABLE-PATH>/workbook/definedNames/definedName</TABLE-PATH>
<COLUMN name="definedName">
<PATH>/workbook/definedNames/definedName/@name</PATH>
<TYPE>character</TYPE>
<DATATYPE>string</DATATYPE>
<LENGTH>200</LENGTH>
</COLUMN>
<COLUMN name="range">
<PATH>/workbook/definedNames/definedName</PATH>
<TYPE>character</TYPE>
<DATATYPE>string</DATATYPE>
<LENGTH>200</LENGTH>
</COLUMN>
</TABLE>
</SXLEMAP>
<!-- Next -->
<SXLEMAP version="2.1">
<TABLE name="tableRef">
<TABLE-PATH>/table</TABLE-PATH>
<COLUMN name="name">
<PATH>/table/@name</PATH>
<TYPE>character</TYPE>
<DATATYPE>string</DATATYPE>
<LENGTH>100</LENGTH>
</COLUMN>
<COLUMN name="id">
<PATH>/table/@id</PATH>
<TYPE>character</TYPE>
<DATATYPE>string</DATATYPE>
<LENGTH>8</LENGTH>
</COLUMN>
<COLUMN name="ref">
<PATH>/table/@ref</PATH>
<TYPE>character</TYPE>
<DATATYPE>string</DATATYPE>
<LENGTH>32</LENGTH>
</COLUMN>
</TABLE>
</SXLEMAP>
;
%macro GetWorkbookItems(file=, member=, mapref=, print=0) ;
%if %sysfunc(fileref(&mapref)) ne 0 %then %do ;
%put %sysfunc(sysmsg()) ;
%abort cancel ;
%end ;
filename src zip "&file" member="&member" ;
filename dst temp ;
%put NOTE: fcopy rc = %sysfunc(fcopy (src, dst)) ;
%if tblref = &mapref %then %do ;
data _null_ ; infile &mapref ; input ; put _infile_ ; run ;
data _null_ ; infile dst ; input ; put _infile_ ; run ;
%end ;
libname member xmlv2 xmlfileref=dst xmlmap=&mapref access=READONLY;
proc copy in=member out=work ;
run ;
%let memnames = '' ;
proc sql noprint ;
select quote(trim(memname)) into :memnames separated by ',' from dictionary.tables
where libname = 'MEMBER' ;
%let memname_count = &sqlobs ;
data _null_ ;
length memname $32 ;
if &memname_count then do memname = &memnames ;
call execute ('data ' || trim(memname) || ';') ;
call execute ("retain member '&member' ;") ;
call execute ("retain mapref '&mapref' ;") ;
call execute ('set ' || trim(memname) || ';') ;
call execute ('run ;') ;
if "&print"="1" then
call execute ('title ' || quote(trim(memname)) || ' ; proc print noobs data=' || trim(memname) || ' ; run ; title ;') ;
end ;
run ;
libname member ;
filename src ;
filename dst ;
%mend GetWorkbookItems ;
%macro ReadExcelTables(workbook_file=) ;
options mprint ; title ;
options nocenter ;
%GetWorkbookItems (file=&workbook_file, member=xl/workbook.xml, mapref=wbkmap) ;
%GetWorkbookItems (file=&workbook_file, member=xl/_rels/workbook.xml.rels, mapref=wbkrel) ;
%* keep only worksheet relationships ;
proc sort data=WorkbookRelationships(where=(target like 'worksheets/%')) ;
by id ;
run ;
title "sheets" ; proc print noobs data=sheets ; run ; title ;
title "definedNames" ; proc print noobs data=definedNames ; run ; title ;
title "workbookRelationships" ; proc print noobs data=workbookRelationships ; run ; title ;
%*-------------------------------------------------------------------- get table sheet+id relationships ;
proc delete data=allTableParts ;
data _null_ ;
set WorkbookRelationships ;
call execute ('%nrstr(%getWorkbookItems)(file=&workbook_file, mapref=wshtbl, member=xl/'||trim(Target)||')') ;
call execute ('proc append base=allTableParts data=sheetTableParts ; run ;') ;
call execute ('proc delete data=sheetTableParts ; run ;') ;
run ;
title "allTableParts" ;
proc sort data=allTableParts; by member Id ; run ;
proc print noobs data=allTableParts ; title ; run ;
%*-------------------------------------------------------------------- ;
proc delete data=allWorksheetRelationships ;
data _null_ ;
set allTableParts ;
by member ;
if first.member ;
length relmember $1000 ;
relmember = trim(transtrn(member,'worksheets/','worksheets/_rels/')) || '.rels' ;
call execute ('%nrstr(%getWorkbookItems)(file=&workbook_file, mapref=wshrel, member='||trim(relmember)||')') ;
call execute ('proc append base=allWorksheetRelationships data=worksheetRelationships ; run ;') ;
call execute ('proc delete data=worksheetRelationships ; run ;') ;
run ;
%if not %sysfunc(exist(allWorksheetRelationships)) %then %do ;
%put NOTE: &workbook_file does not contain any Excel tables ;
%return ;
%end ;
title "allWorksheetRelationships" ;
proc sort data=allWorksheetRelationships; by member Id ; run ;
proc print noobs data=allWorksheetRelationships ; run ; title ;
%*-------------------------------------------------------------------- ;
proc delete data=allTableRefs ;
data _null_ ;
set allWorksheetRelationships ;
length refmember $1000 ;
refmember = 'xl/tables/' || scan(target,-1,'/') ;
put 'NOTE: ' refmember= ;
call execute ('%nrstr(%getWorkbookItems)(file=&workbook_file, mapref=tblref, member='||trim(refmember)||')') ;
call execute ('proc append base=allTableRefs data=tableRef ; run ;') ;
run ;
title "allTableRefs" ;
/*proc sort data=allWorkbooksRelationships; by member Id ; run ;*/
proc print noobs data=allTableRefs ; run ; title ;
proc sql noprint ;
create table allTableRanges as
select
A.name as tableName
, E.name as sheetName
, A.ref as range
from allTableRefs A
join allWorksheetRelationships B on substr(B.Target,3) = substr(A.member,3)
join allTableParts C on C.Id = B.Id and prxchange('s#/?[_.]rels##', 2, B.member) = C.member
join workbookRelationships D on 'xl/'||D.Target = C.member
join sheets E on E.Id = D.Id
;
%macro compact(data=) ;
%let syslast = &data ;
%local libname memname ;
%let libname = %scan(&syslast,1,.) ;
%let memname = %scan(&syslast,2,.) ;
proc sql noprint ;
select
'max(length('||trim(name)||'))' ,
':l' || name ,
trim(name) || ' char(&l' || trim(name) || ')'
into
:measures separated by ',' ,
:intos separated by ',' ,
:modifies separated by ','
from dictionary.columns where libname = "&libname" and memname = "&memname" and type='char'
order by varnum
;
%if &sqlobs = 0 %then %return ;
select &measures into &intos from &data ;
alter table &data modify &modifies ;
quit ;
%mend compact ;
%compact(data=allTableRanges) ;
title "Table Ranges" ;
proc print noobs data=allTableRanges; run ; title ;
filename workbook "&workbook_file" ;
data _null_ ;
do until (lastTable) ;
set allTableRanges end=lastTable;
call execute ('proc import dbms=xlsx file=workbook replace out=' || trim(tableName) || ' ;') ;
call execute ('range='||quote(catx('$',sheetName,range))||' ;') ;
call execute ('run ;') ;
call execute ('title ' || quote(trim(tableName)) || ' ; proc print noobs data=' || trim(tableName) || ' ; run ; title ;') ;
end ;
run ;
filename workbook ;
ods html close ;
%mend ReadExcelTables ;
%ReadExcelTables(workbook_file=&workbook_file) ;
... View more
