11-17-2014 03:30 AM
I could not get the correct formula for getting the absolute_column_width despite getting each column's width by proc contents.
I'm thinking of an alternative in fixing the column widths by VB script instead. Input and output would be in XLS. Then, the VB script is to be called in SAS with data _null_; file "something.vbs";
However, I'm not knowledgeable in VB scripts. Is this possible?
11-17-2014 04:14 AM
WEll, in VBA (note slightly different to VB script), you can do the following:
However I think you should be fixing this when you create the document as you may not end up with what you expect. How are you creating the XSLX? When I do this I have proc report and I fix column widths within the proc report, otherwise if you autofit and one record has lots of text, your report will look wrong. So I suggest doing:
ods tagsets.excelxp file="....xlsx";
proc report style=minimal nowd split="|";
define col1 / "Column1" style(column)=[width=10cm just=l] style(header)=[just=l];
11-18-2014 03:26 AM
Actually I created a XML file first via ods tagsets.excelxp to output the SAS formats. I use a simple proc print statement within the ODS statements.
After the XML output, I used macro statements (which have VB scripts embedded) to convert the XML file to XLS.
I'd like the code on the setting of the column widths be flexible even if the table structure has changed -- such that I do not need to list all the columns one by one and make manual changes on the input widths of the columns.
11-18-2014 04:08 AM
Well if your doing it in VBA (Visual Basic for Applications - Note VB scripting is something else so don't confuse the two), then just add my two (3 if you need to select sheet) lines of code below:
Put that in your VBA sub and it will select Sheet, select all rows, then autofit.
Am still not sure why you need the XLS format, this is an old Office Proprietary format which has been replaced by the Open Office XLSX (which is a Zip file with directories and XML within). Your also better off using proc report rather than print as you can set styles, widths etc. and thus create actual stylized reports, me personally I wouldn't rely on Excel to do that, all it will take is one long string and your whole report will become messed up.
11-26-2014 12:59 AM
Thanks. I have applied AutoFit in VBScript code which I made and executed with SAS.
We had to output XLS tables to maintain what was set in our project.
11-26-2014 03:48 AM
Sounds like someone should re-assess the project, XLS is both proprietary - I.e. closed sourced sourced compiled, and not even the latest version from M$. SDLC review what its doing regularly and update where necessary.