topic Export Multiple datasets into on Excel files with Sheet names in SAS Programming

Export Multiple datasets into on Excel files with Sheet names

SASuserlot — Sun, 26 Mar 2023 23:46:05 GMT

I have two datasets with the same variables and the same formats. How to export them into one excel file with different sheets. Is there any simple way, or do I have to write ODS EXCEL ( or TAGSETS) with PORC REPORT for each dataset? Thank you for your input.

In the following example, is there any way I can create a macro and loop the required datasets? The following is simple export

 data class1;
 set sashelp.class;
 run;

 data class2;
 set sashelp.class;
 run;


PROC EXPORT DATA= WORK.class1
            OUTFILE= "D:\Desktop\classes.xls" 
            DBMS=EXCEL REPLACE;
     SHEET="Class1"; /* for class2 I have to run it again*/
     NEWFILE=YES;
RUN;

This is how I want two sheets in one excel file

Re: Export Multiple datasets into on Excel files with Sheet names

SASKiwi — Mon, 27 Mar 2023 00:04:04 GMT

It's a lot easier if you use the XLSX LIBNAME:

data class1;
 set sashelp.class;
 run;

 data class2;
 set sashelp.class;
 run;

 libname CLASS XLSX "<MyDir>\Class.xlsx";

 proc datasets library = CLASS;
   copy in = WORK  out = CLASS;
  select class1 class2;
  run;
quit;

libname CLASS clear;

No loop needed.

Re: Export Multiple datasets into on Excel files with Sheet names

SASuserlot — Mon, 27 Mar 2023 00:16:31 GMT

Thank you very much. It served my purpose.