Different Formats

[podarum]

Hi,

I'm trying to export to Excel from SAS, but need to uphold a certain set of formats.. Not sure how or if SAS is able to do them.

1. Can the Row of the Excel export be set to a certain height .. eg. 42

2. Vertical alignment to Centre

3. Wrap text

4. Bold font

5. Autofit column widths  eg. 11

6. Format columns to “d-mmm-yy”, width 9

7. Hide a column

8. Delete a column on certain sheets

9. Add a new sheet

10. Set file attribute to read-only

thank you in advance

[RW9 · Accepted solution]

You can use ods excel to create the Excel file, which has options for a lot of what you want, from the docs:

https://support.sas.com/resources/papers/proceedings17/0169-2017.pdf

With this, use proc report to generate your report and then within that report definition you apply specific excel formats to the data:

http://support.sas.com/resources/papers/proceedings13/316-2013.pdf

(whilst the above uses tagsets.excelxp, it should be valid for ods excel as well).

Just to add, your last couple of requests are not possible.  You can read/write data to and from excel, and create sheets by pushing out data to them.  You cannot however, perform other Excel functions such as deleting columns, setting attributes and such like which is Excel specific.  To do such things consider using a VBA macro to load your Excel file, as VBA is embedded into Excel it can take care of that type of functionality.  I would question why you need to do this from a third party app in the first pace however.

[Reeza]

---

@RW9 wrote:

Just to add, your last couple of requests are not possible.  You can read/write data to and from excel, and create sheets by pushing out data to them.  You cannot however, perform other Excel functions such as deleting columns, setting attributes and such like which is Excel specific.  To do such things consider using a VBA macro to load your Excel file, as VBA is embedded into Excel it can take care of that type of functionality.  I would question why you need to do this from a third party app in the first pace however.

---

But you can drop columns ahead of time and/or conditionally for some sheets. 

There's the PROTECT_WORKSHEET option in ODS EXCEL which will set the file to Read Only. 

[Reeza]

---

---

ODS EXCEL and/or Tagsets.ExcelXP can do most of those. 

Options are listed here:

https://support.sas.com/rnd/base/ods/odsmarkup/excelxp_help.html

---

@podarum wrote:

Hi,

I'm trying to export to Excel from SAS, but need to uphold a certain set of formats.. Not sure how or if SAS is able to do them.

1. Can the Row of the Excel export be set to a certain height .. eg. 42

2. Vertical alignment to Centre

3. Wrap text

4. Bold font

5. Autofit column widths  eg. 11

6. Format columns to “d-mmm-yy”, width 9

7. Hide a column

8. Delete a column on certain sheets

9. Add a new sheet

10. Set file attribute to read-only

thank you in advance

---

[ballardw]

If a value is date that you want to display a "d-mmm-yy" you will need a custom format. I think the one below does what you want.

proc format library=work;
picture dashdate
low - high ='%d-%b-%0y'  (datatype=date)
;
run;

data example;
   x='10JAN2009'd;
   put x dashdate.;
run;

the %0y says to insert a zero if the last two digits of a year are less than 10. If you want the day to be two digits with a leading zero use %0d.

If the value is not a SAS date value you need to create the character value somewhere as a format is likely not the want to manipulate character "dates".