Technology Tales

Adventures & experiences in contemporary technology

Adding titles and footnotes to Excel files created using SAS

14th August 2023

Using the Excel and ExcelXP destinations in the Output Delivery System (ODS), SAS can generate reports as XLSX workbooks with one or more worksheets. Recently, I was updating a SAS Macro that created one of these and noticed that there were no footnotes. The fix was a simple: add to the options specified on the initial ODS Excel statement.

ods excel file="&outdir./&file_name..xlsx" options(embedded_titles="yes" embedded_footnotes="yes");

Notice in the code above that there are EMBEDDED_TITLES and EMBEDDED_FOOTNOTES options. Without both of these being set to YES, no titles or footnotes will appear in a given worksheet even if they have been specified in a program using TITLE or FOOTNOTE statements. In my case, it was the EMBEDDED_FOOTNOTES option that was missing, so adding that set things to rights.

The thing applies to the ExcelXP tag set as you will find from a code sample that SAS has shared on their website. That was what led me to the solution to what was happening in the Excel ODS destination in my case.

Solving error code 8000101D in SAS

26th November 2022

Recently, I encountered the following kind of message when reading an Excel file into SAS using PROC IMPORT:

ERROR: Error opening XLSX file -> xxx-.xlsx . It is either not an Excel spreadsheet or it is damaged. Error code=8000101D
Requested Input File Is Invalid
ERROR: Import unsuccessful. See SAS Log for details.

Naturally, thoughts arise regarding the state of the Excel file when you see a message like this but that was not the case because the file opened successfully in Excel and looked OK to me. After searching on the web, I found that it was a file permissions issue. The actual environment that I was using at the time was entimICE and I had forgotten to set up a link that granted read access to the file. Once that was added, the problem got resolved. In other systems, checking on file system permissions is needed even if the message seems to suggest that you are experiencing a file integrity problem.

Shrinking title bar search box in Microsoft Office 365 applications

6th December 2019

Shrinking title bar search box in Microsoft Office 365 applications

It might be a new development but I only recently spotted the presence of a search box in the titles of both Microsoft Word and Microsoft Excel that I have as part of an Office 365 subscription. Though handy for searching file contents and checking on spelling and grammer, I also realised that the boxes take up quite a bit of space and decided to see if hiding them was possible.

Shrinking title bar search box in Microsoft Office 365 applications

In the event, I found that they could be shrunk from a box to an icon that expanded to pop up a box when you clicked on them. Since I did not need the box to be on view all the time, that outcome was sufficient for my designs though it may not satisfy others who want to hide this functionality completely.

Shrinking title bar search box in Microsoft Office 365 applications

To get it, it was a matter of going to File > Options and putting a tick in the box next to the Collapse the Microsoft Search box by default entry in the General tab before clicking on the OK button. Doing that freed up some title bar space as desired and searching is only a button press away.

Migrating to Windows 10

10th August 2015

While I have had preview builds of Windows 10 in various virtual machines for the most of twelve months, actually upgrading physical and virtual devices that you use for more critical work is a very different matter. Also, Windows 10 is set to be a rolling release with enhancements coming on an occasional basis so I would like to see what comes before it hits the actual machines that I need to use. That means that a VirtualBox instance of the preview build is being retained to see what happens to that over time.

Some might call it incautious but I have taken the plunge and completely moved from Windows 8.1 to Windows 10. The first machine that I upgraded was more expendable and success with that encouraged me to move onto others before even including a Windows 7 machine to see how that went. The 30-day restoration period allows an added degree of comfort when doing all this. The list of machines that I upgraded were a VMware VM with 32-bit Windows 8.1 Pro (itself part of a 32-bit upgrade cascade involving Windows 7 Home and Windows 8 Pro), a VirtualBox VM with 64-bit Windows 8.1, a physical PC that dual booted Linux Mint 17.2 and 64-bit Windows 8.1 and an HP Pavilion dm4 laptop (Intel Core i3 with 8 GB RAM and a 1 TB SSHD) with Windows 7.

The main issue that I uncovered with the virtual machines is that the Windows 10 update tool that is downloaded onto Windows 7 and 8.x does not accept the graphics capability on there. This is a bug because the functionality works fine on the Windows Insider builds. The solution was to download the appropriate Windows 10 ISO image for use in the ensuing upgrade. There are 32-bit and 64-bit disk images with Windows 10 and Windows 10 Pro installation files on each. My own actions used both disk images.

During the virtual machine upgrades, most of the applications that considered important were carried over from Windows 8.1 to Windows without a bother. Anyone would expect Microsoft’s own software like Word, Excel and others to make the transition, but others like Adobe’s Photoshop and Lightroom made it too, as did Mozilla’s Firefox, albeit requiring a trip to Settings to set it as the default option for opening web pages. Less well-known desktop applications like Zinio (digital magazines) or Mapyx Quo (maps for cycling, walking and the like) were the same. Classic Shell was an exception but the Windows 10 Start Menu suffices for now anyway. Also, there was a need to reinstate Bitdefender Antivirus Plus using its new Windows 10 compatible installation file. Still, the experience was a big change from the way things used to be in the days when you used to have to reinstall nearly all your software following a Windows upgrade.

The Windows 10 update tool worked well for the Windows 8.1 PC, so no installation disks were needed. Neither was the bootloader overwritten so the Windows option needed selecting from GRUB every time there was a system reboot as part of the installation process, a temporary nuisance that was tolerated since booting into Linux Mint was preserved. Again, no critical software was lost in the process apart from Kaspersky Internet Security, which needed the Windows 10 compatible version installed, much like Bitdefender, or Epson scanning software that I found was easy to reinstall anyway. Usefully, Anquet’s Outdoor Map Navigator (again used for working with walking and cycling maps) continued to function properly after the changeover.

For the Windows 7 laptop, it was much the same story, albeit with the upgrade being delivered using Windows Update. Then, the main Windows account could be connected to my Outlook account to get everything tied up with the other machines for the first time. Before the obligatory change of background picture, the browns in the one that I was using were causing interface items to appear in red, not exactly my favourite colour for application menus and the like. Now they are in blue and all the upheaval surrounding the operating system upgrade had no effect on the Dropbox or Kaspersky installations that I had in place before it all started. If there is any irritation, it is that unpinning of application tiles from the Start Menu or turning off of live tiles is not always as instantaneous as I would have liked and that is all done now anyway.

While writing the above, I could not help thinking that more observations on Windows 10 may follow, but these will do for now. Microsoft had to get this upgrade process right and it does appear that they have, so credit is due to them for that. So far, I have Windows 10 to be stable and will be seeing how things develop from here, especially when those new features arrive occasionally as is the promise that has been made to us users. Hopefully, that will be as painless as it needs to be to ensure trust is retained.

A cautionary tale regarding Excel spreadsheet sharing

10th November 2010

Recently, I encountered a reason to be wary about create shared Excel spreadsheets when one ballooned in size. It ended up growing to around 130 megabytes before I tried turning off sharing to see what happened and the size shrunk to under 200 kilobytes. From this, it would appear that the version control information was the cause of the explosion in file size. With that in mind, I set about to looking through the settings to see if there were any that might need optimisation. The default action is to keep thirty days of change tracking but I have this reduced to a single day in order not to be keeping too much. Quite how much, you need to retain is up to you but I will keeping an eye on things now that I have done this.

One reason why you cannot merge cells in Excel

23rd October 2010

One handy thing that I didn’t realise that you could do with Excel until the last few months was the ability to share an open workbook between users and collate any changes that are made (it seems that a form of version control is behind this). From what I have seen, Excel seems to manage changes to shared spreadsheets rather well. When you save yours, it adds updates from other users and warns if any edits collide with one another. To activate it in Excel 2003, all that needs doing is for you to go to the Share Workbook entry on the Tools and tick the appropriate checkbox in the resulting dialogue box. In 2007 and 2010, look for the Share Workbook icon in the Review tab on the ribbon to get the same dialogue box popping up.

That’s not to say that it doesn’t have its restrictions though and I have found that the merging of cells is made unavailable but that can be sorted by unsharing and resharing the workbook when no one else is using it. As to why cell merger is switched off by sharing, I have a few ideas. Maybe, they couldn’t make it work reliably (can happen with large software development projects like the creation of a new version of Excel) or decided that it would have consequences for other users that are too inconvenient. Either way, we cannot merge cells in shared workbooks and that’s the way that things are for now. Some may not worry about this though since they reckon that cell merging is undesirable anyway; well, don’t go doing it in any spreadsheet that is likely to be read in by another program or you could cause trouble.

Reading data into SAS using the EXCEL and PCFILES library engines

4th March 2010

Recently, I had the opportunity to have a look at the Excel library engine again because I need to read Excel data into SAS. You need SAS Access for PC Files licensed for it to but it does simplify the process of getting data from spreadsheets into SAS. It all revolves around setting up a library pointing at the Excel file using the Excel engine. The result is that every worksheet in the file is treated like a SAS dataset even if there names contain characters that SAS considers invalid for dataset names. The way around that is to enclose the worksheet name in single quotes with the letter n straight after the closing quote, much in the same way as you’d read in text strings as SAS date values (’04MAR2010’d, for example). In order to make all of this more, I have added some example code below.

libname testxl excel 'c:\test.xls';

data test;
set testxl.'sheet1$'n;
run;

All of the above does apply to SAS on Windows (I have used it successfully in 9.1.3 and 9.2) but there seems to be a way of using the same type of thing on UNIX too. Again, SAS Access for PC Files is needed as well as a SAS PC Files server on an available Windows machine and it is the PCFILES engine that is specified. While I cannot say that I have had the chance to see it working in practice but seeing it described in SAS Online Documentation corrected my previous misimpressions about the UNIX variant of SAS and its ability to read in Excel or Access data. Well, you learn something new every day.

Office 2007 on test…

23rd January 2007

With its imminent launch and having had a quick at one of its beta releases, I decided to give Office 2007 a longer look after it reached its final guise. This is courtesy of the demonstration version that can be downloaded from Microsoft’s website; I snagged Office Standard which contains Word, Excel, PowerPoint and Outlook. Very generously, the trial version that I am using gives me until the end of March to come to my final decision.

And what are my impressions? Outlook, the application from the suite that I most use, has changed dramatically since Outlook 2002, the version that I have been using. Unless you open up an email in full screen mode, the ribbon interface so prevalent in other members of the Office family doesn’t make much of an appearance here. The three-paned interface taken forward from Outlook 2003 is easy to get around. I especially like the ability to collapse/expand a list of emails from a particular sender: it really cuts down on clutter. The ZoneAlarm anti-spam plug-in on my system was accepted without any complaint as were all of my PST files. One thing that needed redoing was the IMAP connection to my FastMail webmail account but that was driven more by Outlook warning messages than by necessity from a user experience point of view. I have still to get my Hotmail account going but I lost that connection when still using Outlook 2002 and after I upgraded to IE7.

What do I make of the ribbon interface? As I have said above, Outlook is not pervaded by the new interface paradigm until you open up an email. Nevertheless, I have had a short encounter with Word 2007 and am convinced that the new interface works well. It didn’t take me long to find my way around at all. In fact, I think that they have made a great job of the new main menu triggered by the Office Button (as Microsoft call it) and got all sorts of things in there; the list includes Word options, expanded options for saving files (including the new docx file format, of course, but the doc format has not been discarded either) and a publishing capability that includes popular blogs (WordPress.com, for instance) together with document management servers. Additionally, the new zoom control on the bottom right-hand corner is much nicer than the old drop down menu. As regards the “ribbon”, this is an extension of the tabbed interfaces seen in other applications like Adobe HomeSite and Adobe Dreamweaver, the difference being that the tabs are only place where any function is found because there is no menu back up. There is an Add-ins tab that captures plug-ins to things like Adobe Distiller for PDF creation. Macromedia in its pre-Adobe days offered FlashPaper for doing the same thing and this seems to function without a hitch in Word 2007. Right-clicking on any word in your document not only gives you suggested corrections to misspellings but also synonyms (no more Shift-F7 for the thesaurus, though it is still there is you need it) and enhanced on-the-spot formatting options. A miniature formatting menu even appears beside the expected context menu; I must admit that I found that a little annoying at the beginning but I suppose that I will learn to get used to it.

My use of Outlook and Word will continue, the latter’s blogging feature is very nice, but I haven’t had reason to look at Excel or PowerPoint in detail thus far. From what I have seen, the ribbon interface pervades in those applications too. Even so, my impressions the latest Office are very favourable. The interface overhaul may be radical but it does work. Their changing the file formats is a more subtle change but it does mean that users of previous Office versions will need the converter tool in order for document sharing to continue. Office 97 was the last time when we had to cope with that and it didn’t seem to cause the world to grind to a halt.

Will I upgrade? I have to say that it is very likely given what is available in Office Home and Student edition. That version misses out on having Outlook but the prices mean that even buying Outlook standalone to compliment what it offers remains a sensible financial option. Taking a look at the retail prices on dabs.com confirms the point:

Office Home and Student Edition: £94.61

Office Standard Edition: £285.50

Office Standard Edition Upgrade: £175.96

Outlook 2007: £77.98

Having full version software for the price of an upgrade sounds good to me and it is likely to be the route that I take, if I replace the Office XP Standard Edition installation that has been my mainstay over the last few years. Having been on a Windows 95 > Windows 98 > Windows 98 SE > Windows ME upgrade treadmill and endured the hell raised when reinstallation becomes unavoidable, the full product approach to getting the latest software appeals to me over the upgrade pathway. In fact, I bought Windows XP Professional as the full product in order to start afresh after moving on from Windows 9x.

  • All the views that you find expressed on here in postings and articles are mine alone and not those of any organisation with which I have any association, through work or otherwise. As regards editorial policy, whatever appears here is entirely of my own choice and not that of any other person or organisation.

  • Please note that everything you find here is copyrighted material. The content may be available to read without charge and without advertising but it is not to be reproduced without attribution. As it happens, a number of the images are sourced from stock libraries like iStockPhoto so they certainly are not for abstraction.

  • With regards to any comments left on the site, I expect them to be civil in tone of voice and reserve the right to reject any that are either inappropriate or irrelevant. Comment review is subject to automated processing as well as manual inspection but whatever is said is the sole responsibility of the individual contributor.