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.
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!
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.
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;
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
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");
..
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.
If I was you ,I would like to use LIBNAME statement .
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.
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
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.
Can you show an example ? So we can test it .
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 |
its showing like this after exporting through libname statement.
the formats are missing.
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
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!
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.