04-20-2015 02:31 AM
Good day all,
i want to export 3 datasets into 3 worksheets in singe file using dde.
i have datasets like
no i want to export these 3 into single work book in different sheets through dde.
your valid comments are welcome.
04-20-2015 04:02 AM
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!
04-20-2015 04:50 AM
using ods html with .xls extension is it possible to get the same output.
multiple reports into multiple sheets in a single file.
04-20-2015 06:51 AM
Yes, for tagset just use:
ods tagsets.excelxp file="new.xlsx" options=(sheet_name="xyz");
ods tagsets.excelxp options=(sheet_name="abc");
ods tagset.excelxp close;
04-20-2015 08:04 AM
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.
04-20-2015 09:03 AM
There are plenty of examples out there of using HTML. Its pretty simliar to excelxp (one is XML the other is HTML):
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:
if ... then call define(_cell_,"mso-number-format:=00#00");
04-20-2015 09:11 AM
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.
04-20-2015 08:51 AM
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.
04-20-2015 08:59 AM
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 .
04-20-2015 09:08 AM
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.
04-20-2015 09:17 AM
its showing like this after exporting through libname statement.
the formats are missing.
04-20-2015 09:25 AM
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;