BookmarkSubscribeRSS Feed
AudeP
Fluorite | Level 6

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
Tom
Super User Tom
Super User

Why not just modify the template so that each target location for writing the data is a separate sheet?

AudeP
Fluorite | Level 6

Thank you for your answer Tom. Indeed, if I could have modified the template, i would have worked differently. But maybe you guessed it, this request comes from a "business" section of my company. Moreover, we are talking about a template built years ago, used but a lot of different people, and having lots of inextricably linked spreadsheets. 

DWilson
Pyrite | Level 9

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

 

 

SAS Innovate 2025: Call for Content

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!

Submit your idea!

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
  • 3 replies
  • 858 views
  • 0 likes
  • 3 in conversation