The SAS Output Delivery System and reporting techniques

SAS DDE

Accepted Solution Solved
Reply
Contributor
Posts: 40
Accepted Solution

SAS DDE

HI,

I'm using sas dde to export SAS data in excel, but I'm not able to define the cell's formats. I would like also to define the formats of 5 coloumn(or line) togheter.

have you any idea?

could you suggest to me any documentary necessary to manipulate the cells of excel?

thanks.


Accepted Solutions
Solution
‎09-02-2014 04:32 AM
Esteemed Advisor
Esteemed Advisor
Posts: 6,726

Re: SAS DDE

Well, DDE is old, and whilst still supported it is not known for how long.  I would suggest that you investigate the ExcelXP tagset which is quite powerful.  This can generate an XML file which in turn then gets read into Excel.  You can apply formats and such like in the reporting step.  Note that the latest versions of Excel now utilize a zipped XML based format (rename .XLSX to .ZIP and you can see this).  The old proprietary binary format has been surpassed by the XML one.

Example of tagset:

ods tagsets.excelxp file="...\temp.xlsx" style=statistical;  /* Note your style defines borders/colors etc. */

proc report data=sashelp.class nowd split="~";

     columns name sex weight;

     define name / "Name";

     define sex / "Sex";

     define weight / "Weight" style={tagattr='format:##0.0'};  /* Apply 3.1 format */

run;

ods tagsets.excelxp close;

Another alternative that I often suggest is to export your data to CSV from SAS, then have the Excel application load this CSV data into a template Excel file (write a simple VBA macro - plenty of examples on the web e.g. Excel VBA open CSV file and import | Making the world simple through software.).  You then have the full functionality of Excel.

View solution in original post


All Replies
Solution
‎09-02-2014 04:32 AM
Esteemed Advisor
Esteemed Advisor
Posts: 6,726

Re: SAS DDE

Well, DDE is old, and whilst still supported it is not known for how long.  I would suggest that you investigate the ExcelXP tagset which is quite powerful.  This can generate an XML file which in turn then gets read into Excel.  You can apply formats and such like in the reporting step.  Note that the latest versions of Excel now utilize a zipped XML based format (rename .XLSX to .ZIP and you can see this).  The old proprietary binary format has been surpassed by the XML one.

Example of tagset:

ods tagsets.excelxp file="...\temp.xlsx" style=statistical;  /* Note your style defines borders/colors etc. */

proc report data=sashelp.class nowd split="~";

     columns name sex weight;

     define name / "Name";

     define sex / "Sex";

     define weight / "Weight" style={tagattr='format:##0.0'};  /* Apply 3.1 format */

run;

ods tagsets.excelxp close;

Another alternative that I often suggest is to export your data to CSV from SAS, then have the Excel application load this CSV data into a template Excel file (write a simple VBA macro - plenty of examples on the web e.g. Excel VBA open CSV file and import | Making the world simple through software.).  You then have the full functionality of Excel.

Contributor
Posts: 40

Re: SAS DDE

Thank you RW9,

but...

can you recommend to me some article in order to help me to set proc report?

I would like to understand how to set the cell's foreground or the style of the words that I will put on the cell....

Esteemed Advisor
Esteemed Advisor
Posts: 6,726

Re: SAS DDE

Proc report is quite a wide subject.  Here are a couple of hits from Google on that.

General syntax: http://support.sas.com/documentation/cdl/en/proc/61895/HTML/default/viewer.htm#a002473620.htm

Some additional:

http://support.sas.com/rnd/papers/sgf07/sgf2007-report.pdf

http://www2.sas.com/proceedings/sugi28/015-28.pdf

You will also need to understand (for any report destination now) proc template and styles.  These are the instructions on formatting of the output.  Can contain background/foreground colors, fonts etc.  Some intital hits on Google:

http://www2.sas.com/proceedings/sugi31/112-31.pdf

http://www2.sas.com/proceedings/sugi29/246-29.pdf

Its worth learning all the above as from my experience it is widely used above other output procedures such as print/tabulate, and styles apply to any outputs.

Grand Advisor
Posts: 16,388

Re: SAS DDE

Here's a reference for the ExcelXP features. The feature is the in the first column and paper reference in the last, with the option in the second. If you're brand new to ExcelXP you should also consider skimming all the papers before you start.

http://www.sas.com/events/cm/867226/ExcelXPPaperIndex.pdf

This is the official SAS quick reference

Base SAS: Quick Reference for TAGSETS.EXCELXP Tagset

If you want DDE the discussion here has the relevant links:

Post a Question
Discussion Stats
  • 4 replies
  • 468 views
  • 6 likes
  • 3 in conversation