BookmarkSubscribeRSS Feed
Ravikumarkummari
Quartz | Level 8

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.

18 REPLIES 18
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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!

Ravikumarkummari
Quartz | Level 8

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

Ravikumarkummari
Quartz | Level 8

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

..


Ksharp
Super User

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

Ravikumarkummari
Quartz | Level 8

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.

Ksharp
Super User

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

Ravikumarkummari
Quartz | Level 8

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.

Ksharp
Super User

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

Ravikumarkummari
Quartz | Level 8
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
Ravikumarkummari
Quartz | Level 8

its showing like this after exporting through libname statement.

the formats are missing.

Ksharp
Super User

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 18 replies
  • 2508 views
  • 0 likes
  • 4 in conversation