topic Re: IMPORT: XLSX / read the footnote in SAS Procedures

IMPORT: XLSX / read the footnote

KevinViel — Wed, 15 Feb 2017 14:38:21 GMT

Using the IMPORT procedure with DBMS = XLXL, is it possible to read the footnote to an Excel worksheet? In page view, the footnote appears only on the first page, almost like a legend box.

Thank you,

Kevin

Re: IMPORT: XLSX / read the footnote

ballardw — Wed, 15 Feb 2017 15:59:21 GMT

I don't believe you will have much luck with proc import. Proc Import is concerned with data that appears in columns and rows of a table-like area. The header and footer of Excel are not in the data of the worksheet.

As a test, save a spreadsheet to a csv file (I recommend a small example with a header or footer) and then open that CSV file in a TEXT editor such as Wordpad, Notepad or even the SAS editor. You may see column headings but you will not have the header or footer appear. The data exported to the csv is pretty much what proc import is allowed to see by the Excel engine.

I don't know enough VB but I suspect that a VB macro could be constructed to send header/footer information to a separate text file that you could read.

Re: IMPORT: XLSX / read the footnote

KevinViel — Wed, 15 Feb 2017 18:55:23 GMT

Thanks, BallardW. That was my suspicion, too, but I figured I might find otherwise.

I wonder if DDE might get it, at least by sending keys to Excel, but I do not have Excel installed on that server, so copy and paste will have to suffice until we can get the lab to send us the data in another format.

Thank you,

Kevin

Re: IMPORT: XLSX / read the footnote

ChrisHemedinger — Wed, 15 Feb 2017 19:18:59 GMT

If it's a true XLSX file, you might get lucky by using the ZIP filename method. I have several blog posts that show how to use this method to read the contents of ZIP files in SAS -- and the XLSX file is a ZIP format!

filename xl ZIP "c:\temp\footnote.xlsx";

data _null_;
 /* requires some knowledge of which sheet */
 infile xl(xl/worksheets/sheet1.xml);
 input;

 /* Discovered by examining the XLSX file in a zip tool */
 pos=find(_infile_,'<headerFooter>');
 if pos > 0 then do;
   end = find(_infile_,'</headerFooter>');
   fnContent = substr(_infile_,pos+14,end-(pos+14));
   put fnContent;
 end;
run;
filename xl clear;

Log from my test:

NOTE: The infile library XL is:
      Directory=c:\temp\footnote.xlsx

NOTE: The infile XL(xl/worksheets/sheet1.xml) is:
      Filename=c:\temp\footnote.xlsx,
      Member Name=xl/worksheets/sheet1.xml

<oddFooter>&amp;LThis is a footnote</oddFooter>

Re: IMPORT: XLSX / read the footnote

rogerjdeangelis — Wed, 15 Feb 2017 19:49:12 GMT

May be able to do this with 'proc import'?

I am ignorant of 'proc import' and I know
you prefer 'proc import'.

Here is one way to do it with linbame.
(more flexible with R/Python/Perl)

HAVE
====

d:/xls/footnotes.xlsx

 +-------------------------+------+-----------+------------
 |      |    A      |   B  |   B  |    C      |    D      |
 +------+-----------+------+------+-----------+-----------+
 |      |           |      |      |           |           |
 |    1 |   NAME    |   AGE|  SEX |  HEIGHT   |  WEIGHT   |
 |    2 |   Alfred  |   14 |   M  |   55      |   96      |
 |    3 |   Alice   |   13 |   F  |   44      |   87      |
 |    4 |   Barbara |   13 |   F  |   48      |   88      |
 |    5 |   Carol   |   14 |   F  |   56      |   99      |
 |    6 |   Henry   |   14 |   M  |   52      |   84      |
 | ...  |   ...     |  ... | ...  |   ..      |   ...     |
 +------------------+------+------+-----------+-----------+

 footnote1 first footnote
 footnote2 second footnote


 [FOOTNOTES]


WANT (SAS DATASET WITH FOOTNOTES)

You can easily operate on the footnotes (last two rows for instance)

Up to 40 obs from class total obs=22

Obs    NAME           SEX           AGE          HEIGHT          WEIGHT

  1    Alfred          M             14              69           112.5
  2    Alice           F             13            56.5              84
  3    Barbara         F             13            65.3              98
  4    Carol           F             14            62.8           102.5
  5    Henry           M             14            63.5           102.5
  6    James           M             12            57.3              83
  7    Jane            F             12            59.8            84.5
  8    Janet           F             15            62.5           112.5
  9    Jeffrey         M             13            62.5              84
 10    John            M             12              59            99.5
 11    Joyce           F             11            51.3            50.5
 12    Judy            F             14            64.3              90
 13    Louise          F             12            56.3              77
 14    Mary            F             15            66.5             112
 15    Philip          M             16              72             150
 16    Robert          M             12            64.8             128
 17    Ronald          M             15              67             133
 18    Thomas          M             11            57.5              85
 19    William         M             15            66.5             112
 20                                   0               0               0
 21    footnote1 first footnote       0               0               0
 22    footnote2 second footnote      0               0               0


WORKING CODE
============

  set xel.'footnotes$'n(
            dbsastype=
             (
               Name   = 'char(64)'
               Sex    = 'char(1)'
               Age    = 'numeric'
               Weight = 'numeric'
               Height = 'numeric'
             ));

FULL SOLUTION
=============


* create example sheet;
title;
%utlfkil(); * delte if exists;
ods excel file="d:/xls/footnotes.xlsx";
ods excel options(
         sheet_name="footnotes"
         sheet_interval="none"
         embedded_titles            = 'yes'
         embedded_footnotes         = 'yes'
);
footnote1 "footnote1 first footnote";
footnote2 "footnote2 second footnote";
proc report data=sashelp.class nowd;
run;quit;
ods excel close;

SOLUTION
========

libname xel "d:/xls/footnotes.xlsx" scan_text=no;
data class;
  set xel.'footnotes$'n(
            dbsastype=
             (
               Name   = 'char(64)'
               Sex    = 'char(1)'
               Age    = 'numeric'
               Weight = 'numeric'
               Height = 'numeric'
             ));
;
run;quit;
libname xel clear;


/*
Up to 40 obs from class total obs=22

Obs    NAME           SEX           AGE          HEIGHT          WEIGHT

  1    Alfred          M             14              69           112.5
  2    Alice           F             13            56.5              84
  3    Barbara         F             13            65.3              98
  4    Carol           F             14            62.8           102.5
  5    Henry           M             14            63.5           102.5
  6    James           M             12            57.3              83
  7    Jane            F             12            59.8            84.5
  8    Janet           F             15            62.5           112.5
  9    Jeffrey         M             13            62.5              84
 10    John            M             12              59            99.5
 11    Joyce           F             11            51.3            50.5
 12    Judy            F             14            64.3              90
 13    Louise          F             12            56.3              77
 14    Mary            F             15            66.5             112
 15    Philip          M             16              72             150
 16    Robert          M             12            64.8             128
 17    Ronald          M             15              67             133
 18    Thomas          M             11            57.5              85
 19    William         M             15            66.5             112
 20                                   0               0               0
 21    footnote1 first footnote       0               0               0
 22    footnote2 second footnote      0               0               0
*/

Re: IMPORT: XLSX / read the footnote

KevinViel — Thu, 02 Mar 2017 21:04:12 GMT

Chris,

Thanks, I was aware of your posts and I have used several of them concerning ZIP. I am close to a solution. It seems the LRECL is an issue. The Excel Worksheet has about 25 columns and 70 rows. I get five "observations":

data _null_;

/* requires some knowledge of which sheet */

infile xl (xl/worksheets/sheet1.xml) lrecl = 500 ;

input;

/* Discovered by examining the XLSX file in a zip tool */

pos=find(_infile_,'<headerFooter>');

pos2=find(_infile_,'<firstFooter>');

pos3=find(_infile_,'Unit');

pos4=find(_infile_,'BLQ');

pos5=find(_infile_,'NS');

put _n_= pos= pos2= pos3= pos4= pos5= _infile_ ;

if pos > 0 then do;

end = find(_infile_,'</headerFooter>');

fnContent = substr(_infile_,pos+14,end-(pos+14));

put fnContent;

end;

run;

_N_=1 pos=0 pos2=0 pos3=0 pos4=0 pos5=0 <?xml version="1.0" encoding="UTF-8" standalone="yes"?>

NOTE: Truncation has occurred on the source line.

_N_=2 pos=0 pos2=0 pos3=0 pos4=0 pos5=0

<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships"><dimension ref="A1:X61"/><sheetViews><sheetView tabSelected="1" zoomScaleNormal="100" workbookViewId=

"0"><pane xSplit="2" ySplit="2" topLeftCell="C3" activePane="bottomRight" state="frozen"/><selection pane="topRight" activeCell="C1" sqref="C1"/><selection pane="bottomLeft" activeCell="A3" sqref="A3"/><selection pane="bottomRight" activeCell="

_N_=3 pos=0 pos2=0 pos3=0 pos4=1 pos5=0 BLQ: Below LOQ (Lower Limit of Quantitation)

_N_=4 pos=0 pos2=0 pos3=0 pos4=0 pos5=0 LOQ: 10.211pg/mL

_N_=5 pos=0 pos2=0 pos3=0 pos4=0 pos5=1 NS: No Sample&C&"Times New Roman,Regular"&9Page &P of &N</firstFooter></headerFooter></worksheet>

NOTE: A total of 5 records were read from the infile library XL.

The minimum record length was 16.

The maximum record length was 500.

NOTE: 5 records were read from the infile XL(xl/worksheets/sheet1.xml).

The minimum record length was 16.

The maximum record length was 500.

One or more lines were truncated.

NOTE: DATA statement used (Total process time):

real time 0.00 seconds

cpu time 0.01 seconds

If I change to RECFM = N

data _null_;

infile xl (xl/worksheets/sheet1.xml) lrecl = 500 recfm = N ;

input;

put _infile_ ;

run ;

Then I can see me desired footnotes:

C&"Times New Roman,Bold"&9&F</oddHeader><oddFooter>&C&"Times New Roman,Regular"&9Page &P of &N</oddFooter><firstHeader>&C&"Times New Roman,Bold"&9&F</firstHeader><firstFooter>&L&"Times New

Roman,Regular"&9Concentration Unit: pg/mL

BLQ: Below LOQ (Lower Limit of Quantitation)

LOQ: 10.211pg/mL

NS: No Sample&C&"Times New Roman,Regular"&9Page &P of &N</firstFooter></headerFooter></worksheet>

NOTE: A total of 131 records were read from the infile library XL.

NOTE: 131 records were read from the infile XL(xl/worksheets/sheet1.xml).

NOTE: DATA statement used (Total process time):

real time 0.03 seconds

cpu time 0.03 seconds

Thank you,

Kevin