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.
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.
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.
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....
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.
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:
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.