topic Re: how to export multiple datasets into multiple worksheets in single file using dde in ODS and Base Reporting

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

Ravikumarkummari — Mon, 20 Apr 2015 06:31:51 GMT

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.

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

RW9 — Mon, 20 Apr 2015 08:02:16 GMT

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!

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

Ravikumarkummari — Mon, 20 Apr 2015 08:50:19 GMT

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.

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

RW9 — Mon, 20 Apr 2015 10:51:10 GMT

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;

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

Ravikumarkummari — Mon, 20 Apr 2015 12:04:30 GMT

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

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

Ksharp — Mon, 20 Apr 2015 12:41:30 GMT

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

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

Ravikumarkummari — Mon, 20 Apr 2015 12:51:03 GMT

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.

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

Ksharp — Mon, 20 Apr 2015 12:59:53 GMT

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

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

RW9 — Mon, 20 Apr 2015 13:03:12 GMT

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");

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

Ravikumarkummari — Mon, 20 Apr 2015 13:08:00 GMT

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.

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

Kurt_Bremser — Mon, 20 Apr 2015 13:11:45 GMT

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.

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

Ksharp — Mon, 20 Apr 2015 13:13:32 GMT

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

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

Ravikumarkummari — Mon, 20 Apr 2015 13:15:56 GMT

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

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

Ravikumarkummari — Mon, 20 Apr 2015 13:17:01 GMT

its showing like this after exporting through libname statement.

the formats are missing.

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

Ksharp — Mon, 20 Apr 2015 13:25:49 GMT

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

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

Ravikumarkummari — Mon, 20 Apr 2015 13:35:49 GMT

i have one doubt in this the variable "new" is which format character or numeric. is it belongs to numeric then not a problem.

if it is character then there is a problem. client need this also into numeric format.

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

Ravikumarkummari — Mon, 20 Apr 2015 13:47:58 GMT

just now i checked the variable new is in character type. so it is not possible. the resultant dataset is also is in numeric format.

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

Ravikumarkummari — Mon, 20 Apr 2015 14:05:51 GMT

can you give me the syntax of call define for comma and percent format.

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

Ksharp — Mon, 20 Apr 2015 14:13:57 GMT

I think Cythina@sas has already answer your question , Check her code in your post .