Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Home
- /
- Programming
- /
- ODS Reports
- /
- From DDE to ODS Excel - How to position 2 tables at different location...

Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Posted 03-29-2019 12:10 PM
(857 views)

Dears,

I've been asked to find a solution in order to prevent any further use of DDE in my company.

I've done a lot of test using ods excel options (sheet_name, sheet_interval, start_at) and PROC REPORT (for the noheader option) to reproduce what was done with DDE. However i 'm stuck because DDE allows to choose multiples position when writing SAS tables to Excel. On the other side, with ODS Excel, once "start_at" option is set, it can't be changed later. Is there some another way to allow writing two different tables on the same spreadsheet at chosen position? As an example, i should be able to write the first table(10 rows) at column 3, row 7 (ok no prob), then the second one at column 3, row 30...

Here is an subset of "DDE" program i should convert to ODS Excel:

filename cell1 dde "Excel|[draft.xlsm]dde DM P!R7C3:R7C4" notab; data _null_; file cell1; set dset1(where=(type='PARTICUL')); retain ht '09'x; put nbre_oth ht nbre_oth2 ; run; ...... filename cell1 dde "Excel|[draft.xlsm]dde DM P!R30C3:R34C4" notab; data _null_; file cell1; set dset1(where=(type='PARTICUL')); retain ht '09'x; put nbre_smth ht nbre_smth1 ; run;

So, indeed i can set the first table at the same kind of position,but not the second one:

ods listing close; ods excel file="someDummyPath\Test_ODS.xlsx" options(sheet_name="dde DM P" sheet_interval="none" start_at="3,7"); proc report data=dset1(where=(type='PARTICUL')) noheader nowd;

column nbre_oth nbre_oth2 ;

define nbre_oth/DISPLAY;

define nbre_oth2/DISPLAY; run; ods excel options(start_at="3,30"); /*not working as indicated in documentation*/ proc report data=dset1(where=(type='PARTICUL')) noheader nowd;

column nbre_smth nbre_smth1;

define nbre_smth/DISPLAY;

define nbre_smth1/DISPLAY; run; ods excel close;

To this point, i searched the web but was not able to find a workaround. And I don't know if there are other methods allowing to reproduce what my colleagues currently produce using DDE (multiple tables written at different position, on same and different spreadsheets + multiple update of the same excel file). To give you some more context, they use a excel template file containing lots of formula and all they need is to fill specific cells each month with SAS data they have computed. This is why position is so important here.

Any advice on this issue is more than welcome. Thank you very much.

3 REPLIES 3

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

I don't know if this is possible with SAS without DDE. The solution my company came up with to replace DDE is to use R with SAS.

Use SAS/IML to push data to be written to excel to R

Use SAS/IML to submit R code to R that actually writes the data to an excel file

We use either the XLConnect R package or the Openxlsx R package to support writing to excel files.

See this paper for example code to get you started:

https://analytics.ncsu.edu/sesug/2017/SESUG2017_Paper-109_Final_PDF.pdf

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!

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.