BookmarkSubscribeRSS Feed
angeliquec
Quartz | Level 8

Hi,

This question is related to a question in a previous thread:

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?

Thank you!

5 REPLIES 5
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Hi,

WEll, in VBA (note slightly different to VB script), you can do the following:

Cells.Select

Cells.EntireColumn.Autofit

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="|";

     columns _all_;

     define     col1 / "Column1" style(column)=[width=10cm just=l] style(header)=[just=l];

     ...

run;

angeliquec
Quartz | Level 8

Hi RW9,

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Hi,

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:

    Sheets("Sheet1").Select

    Cells.Select

    Cells.EntireColumn.AutoFit

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.

angeliquec
Quartz | Level 8

Hi RW9,

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 5620 views
  • 0 likes
  • 2 in conversation