The SAS Output Delivery System and reporting techniques

how to export multiple datasets into multiple worksheets in single file using dde

Reply
Frequent Contributor
Posts: 111

how to export multiple datasets into multiple worksheets in single file using dde

Good day all,

i want to export 3 datasets into 3 worksheets in singe file using dde.

i have datasets like

sashelp.class

sashelp.cars

sashelp.shoes

no i want to export these 3 into single work book in different sheets through dde.

your valid comments are welcome.

Super User
Super User
Posts: 7,695

Re: how to export multiple datasets into multiple worksheets in single file using dde

IMO don't.  DDE is very old technology now, how long it will be supported for/work is unknown and the functionality is limited.  There are a variety of methods for getting data to Excel which are supported:

tagsets.excelxp - this is probably the easiest to use and has plenty of options

libname to excel - for < 9.4 I wouldn't recommend it, however in 9.4 libname to XLSX is present I believe.  It doesn't have all the formatting options that tagset does, but can write directly to and existing file.

datastep export to CSV - Excel can read CSV easily, file size is small, good for transfer, but doesn't have formatting.

proc export - for if you get really desperate!

Frequent Contributor
Posts: 111

Re: how to export multiple datasets into multiple worksheets in single file using dde

ok agreed.

using ods html with .xls extension is it possible to get the same output.

multiple reports into multiple sheets in a single file.

Super User
Super User
Posts: 7,695

Re: how to export multiple datasets into multiple worksheets in single file using dde

Yes, for tagset just use:

ods tagsets.excelxp file="new.xlsx" options=(sheet_name="xyz");

proc report...;

ods tagsets.excelxp options=(sheet_name="abc");

proc report...;

ods tagset.excelxp close;

Frequent Contributor
Posts: 111

Re: how to export multiple datasets into multiple worksheets in single file using dde

You didn't what i have said i know it is possible through ods tagsets.

ods html with .xls extension same process is possible i want to know.

Thanks

Super User
Super User
Posts: 7,695

Re: how to export multiple datasets into multiple worksheets in single file using dde

There are plenty of examples out there of using HTML.  Its pretty simliar to excelxp (one is XML the other is HTML):

http://support.sas.com/resources/papers/proceedings12/150-2012.pdf

With regards to your point: "libname and ods tagsets.excelxp is not supported using this procedure to display in output file displaying format."

Libname I am not sure about without checking, but excelxp *does* have the ability to have formats just like any ods destination.

Its all to do with the style options you put in the output procedure, nothing really to do with the destination, e.g:

define a_column / style=("mso-number-format:=00#00");

Or if you want it to be on a per cell use call define:

compute a_column;

     if ... then call define(_cell_,"mso-number-format:=00#00");

..


Super User
Posts: 7,400

Re: how to export multiple datasets into multiple worksheets in single file using dde

But on top of everything, you can never be sure what that execrable Excel does when it reads the data.

Stay away from that POS, as far as you can.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 9,865

Re: how to export multiple datasets into multiple worksheets in single file using dde

If I was you ,I would like to use LIBNAME statement .

Frequent Contributor
Posts: 111

Re: how to export multiple datasets into multiple worksheets in single file using dde

I used libname also for exporting.but what the problem was using libname its missing formats in output file.

i already told you in previous i have a format of fmt32 this will apply to all character variables and changing it to numeric variables with fmt32 format.

libname and ods tagsets.excelxp is not supported using this procedure to display in output file displaying format.

So it is only possible to display the formats using ods html with .xls extension. I think have you understood what i said.

Using ods html with .xls extension to send multiple reports to multiple sheets in a single file,

or using DDE send multiple datasets into multiple sheets in a single file.

please help me solve this problem.

Super User
Posts: 9,865

Re: how to export multiple datasets into multiple worksheets in single file using dde

Did you check the code I posted to you before. Add a character like TAB '09'x into your variable ,and your format will be retained as 43.3%  3,456,456.87 

It doesn't matter with LIBNAME .

Xia Keshan

Frequent Contributor
Posts: 111

Re: how to export multiple datasets into multiple worksheets in single file using dde

i tried with libname statement. but the variable is showing no format after exporting.

i.e. it is showing 0.043  3456456 just like this.

Super User
Posts: 9,865

Re: how to export multiple datasets into multiple worksheets in single file using dde

Can you show an example ? So we can test it .

Frequent Contributor
Posts: 111

Re: how to export multiple datasets into multiple worksheets in single file using dde

413
45582372
242
27433526
203
23314259
0.85
0.839
156
47
4207109
0.114
0.092
0.194
0.153
0.232
0.18
Frequent Contributor
Posts: 111

Re: how to export multiple datasets into multiple worksheets in single file using dde

its showing like this after exporting through libname statement.

the formats are missing.

Super User
Posts: 9,865

Re: how to export multiple datasets into multiple worksheets in single file using dde

You should us the code you tried . exist SAS and open c:\temp\w.xls


proc format;
value fmt
 0-1=[percent8.2]
 other=[comma20.2];
run;

data have;
 input x;
 format x fmt.;
cards;
455823.72
242
274335.26
203
233142.59
0.85
0.839
;
run;
data want;
 set have;
 length new $ 40;
 new=cats(vvalue(x),'09'x);
 drop x;
run;
libname x excel 'c:\temp\w.xls';
data x.want;
 set want;
run;


Xia Keshan

Ask a Question
Discussion stats
  • 18 replies
  • 1306 views
  • 0 likes
  • 4 in conversation