how to export two datasets into single work sheet in excel

[Ravikumarkummari]

Good day all,

i have two datasets for example sashelp.class1 sashelp.class2;

now i want to export these two datasets into excel within single work sheet.

sheet1 contains first sashelp.class1 data and under sashelp.class2 data.

your valid comments are welcome.

[Kurt_Bremser]

- combine the datasets into one using the data step or proc sql, and then export that

- use ODS TAGSETS.EXCELXP to write both datasets into a single sheet

[RB1Kenobi]

Hello

Tagsets is a good way to export to Excel.  I find it quick to tweak what I know to suit different requirements (e.g. apply formatting).

DDE is another technique but personally I found it a bit slow going as you have to define table ranges and locations specifically.  Worth reading up the coding though as it may prove handy.

I could not attach the file for some reason, so I've pasted an example below for you:

/***** EXCEL OUTPUT *******/

ods tagsets.ExcelXP

path= "###### type pathway here ##"  /* Pathway  ".....\...\..\.."   */

file="two datasets.xls" /* filename wil XLS extention */

style=printer;

ods escapechar="^";

  /* Define the worksheet parameters */

  ods tagsets.ExcelXP

  options(embedded_titles='yes' embedded_footnotes='yes'

  sheet_interval='none'  /* Sheet interval determines whether to print on to the same sheet or a new one. */

  sheet_name="Two datasets"

     zoom='80'

  skip_space='5,0,0,0,3'   /* Determines the number of rows between the two tables. */

  );

  run;

  /* You can embed style elements within the proc print */

  /* Dataset 1 */

  proc print data= sashelp.class

  width=minimum 

  label

  style (data) = [font_face=calibri just=left ]  noobs;

  label  age = "^{style[just=center fontweight=bold] AGE of Pupil}";

  run;title;footnote;

  /* Dataset 2 */

  proc print data= sashelp.fish (obs=100)

  width=minimum 

  label

  style (data) = [font_face=calibri just=left]  noobs;

  run;title;footnote;

ods _all_ close;

ods listing;