topic SAS DDE in ODS and Base Reporting

SAS DDE

Rakeon — Tue, 02 Sep 2014 08:26:38 GMT

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.

Re: SAS DDE

RW9 — Tue, 02 Sep 2014 08:32:47 GMT

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.

Re: SAS DDE

Rakeon — Tue, 02 Sep 2014 12:14:33 GMT

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....

Re: SAS DDE

RW9 — Tue, 02 Sep 2014 12:30:04 GMT

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.

Re: SAS DDE

Reeza — Tue, 02 Sep 2014 13:30:13 GMT

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: