BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Rakeon
Quartz | Level 8

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.

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

4 REPLIES 4
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

Rakeon
Quartz | Level 8

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

Reeza
Super User

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:

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
  • 4 replies
  • 1456 views
  • 6 likes
  • 3 in conversation