DDE-Dynamic Data Exchange, exporting a sas data set to excel

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 81
Accepted Solution

DDE-Dynamic Data Exchange, exporting a sas data set to excel

Hi All,

Can anybody explain the below mention code......how does it work especially "r6c1:r8c3" and how can we figure out the path in which the sas data set will be exported to the excel file.

filename bigout dde ‘EXCEL|loading!r6c1:r8c3’;

data _null_; /* no need to create a new dataset */

set bigfile;

file bigout;

put yearmth req paid;

run;

Regards

Anand


Accepted Solutions
Solution
‎09-05-2013 03:57 PM
PROC Star
Posts: 7,363

Re: DDE-Dynamic Data Exchange, exporting a sas data set to excel

The code assumes that a workbook is open in Excel, that the workbook contains a worksheet labeled 'loading' (although the quotes cannot be smart quotes like in your example), and that bigfile only contains three records.  The code writes the three records to the worksheet, with the first records written to row 6, the second record to row 7, and the third record written to row eight.  All three fields are written to the first 3 columns.

e.g., the following will do that with sashelp,class:

filename bigout dde 'EXCEL|loading!r6c1:r8c3';

data _null_; /* no need to create a new dataset */

  set sashelp.class;

  file bigout;

  if _n_ le 3 then do;

    put name age height;

  end;

run;

View solution in original post


All Replies
Solution
‎09-05-2013 03:57 PM
PROC Star
Posts: 7,363

Re: DDE-Dynamic Data Exchange, exporting a sas data set to excel

The code assumes that a workbook is open in Excel, that the workbook contains a worksheet labeled 'loading' (although the quotes cannot be smart quotes like in your example), and that bigfile only contains three records.  The code writes the three records to the worksheet, with the first records written to row 6, the second record to row 7, and the third record written to row eight.  All three fields are written to the first 3 columns.

e.g., the following will do that with sashelp,class:

filename bigout dde 'EXCEL|loading!r6c1:r8c3';

data _null_; /* no need to create a new dataset */

  set sashelp.class;

  file bigout;

  if _n_ le 3 then do;

    put name age height;

  end;

run;

Frequent Contributor
Posts: 81

Re: DDE-Dynamic Data Exchange, exporting a sas data set to excel

so where can i find that exported whoksheet labeled 'loading'.

and i got the error on running this code i.e.

ERROR: Physical file does not exist, EXCEL|loading!r6c1:r8c3.

Super Contributor
Posts: 1,636

Re: DDE-Dynamic Data Exchange, exporting a sas data set to excel

try to open an empty excel file and rename "sheet1" to "loading" or change Art's program to

filename bigout dde 'EXCEL|sheet1!r6c1:r8c3';

data _null_; /* no need to create a new dataset */

  set sashelp.class;

  file bigout;

  if _n_ le 3 then do;

    put name age height;

  end;

run;

Super User
Posts: 5,256

Re: DDE-Dynamic Data Exchange, exporting a sas data set to excel

Be aware of that DDE is kind of old school, and not fully supported by Microsoft.

More up to date alternatives are using

  • PROC EXPORT with the XLSX engine
  • Excel LIBANME engine (maybe together with data step modify which let you have some control of the output)
  • Add-in for MS Office (part of BI Server and Visual Analytics packages, among others) - let you have almost full control of output/design.
Data never sleeps
Super Contributor
Posts: 1,636

Re: DDE-Dynamic Data Exchange, exporting a sas data set to excel

Hi Art,

what do you mean by

(although the quotes cannot be smart quotes like in your example)?

Thank you!

PROC Star
Posts: 7,363

Re: DDE-Dynamic Data Exchange, exporting a sas data set to excel

Hi Linlin!  When copied and pasted the OP's code, the quotes came across as smart quotes (see, e.g.: Smart Quotes - Fonts.com., and SAS didn't like them.

Super Contributor
Posts: 1,636

Re: DDE-Dynamic Data Exchange, exporting a sas data set to excel

Thanks Art! I looked at the link, it is so funny. it is my first time to notice the difference.

Frequent Contributor
Posts: 81

Re: DDE-Dynamic Data Exchange, exporting a sas data set to excel

Aurthur & Linlin, I thanks and appreciate your explanation as it helped me to understand how this DDE functionality works.

Thanks

Anand

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 320 views
  • 8 likes
  • 4 in conversation