BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
KevinViel
Pyrite | Level 9

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

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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. 

View solution in original post

5 REPLIES 5
ballardw
Super User

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. 

KevinViel
Pyrite | Level 9
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
ChrisHemedinger
Community Manager

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>

 

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.
KevinViel
Pyrite | Level 9

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&amp;C&amp;"Times New Roman,Regular"&amp;9Page &amp;P of &amp;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&amp;"Times New Roman,Bold"&amp;9&amp;F</oddHeader><oddFooter>&amp;C&amp;"Times New Roman,Regular"&amp;9Page &amp;P of &amp;N</oddFooter><firstHeader>&amp;C&amp;"Times New Roman,Bold"&amp;9&amp;F</firstHeader><firstFooter>&amp;L&amp;"Times New

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

BLQ: Below LOQ (Lower Limit of Quantitation)

LOQ: 10.211pg/mL

NS: No Sample&amp;C&amp;"Times New Roman,Regular"&amp;9Page &amp;P of &amp;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

rogerjdeangelis
Barite | Level 11
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
*/

sas-innovate-2024.png

 

Time is running out to save with the early bird rate. Register by Friday, March 1 for just $695 - $100 off the standard rate.

 

Check out the agenda and get ready for a jam-packed event featuring workshops, super demos, breakout sessions, roundtables, inspiring keynotes and incredible networking events. 

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 1356 views
  • 0 likes
  • 4 in conversation