Help using Base SAS procedures

Exporting Multiple Data Sets to Multiple Worksheets in one Excel Workbook

Accepted Solution Solved
Reply
Contributor
Posts: 59
Accepted Solution

Exporting Multiple Data Sets to Multiple Worksheets in one Excel Workbook

SAS EG Version 4.3.010196

I'm currently running a macro that creates multiple outputs based on a given condition.  I want to option to export each data set to the same workbook on it's own individual tab.

For HIPPA reason I can't include any data, but regardless of the data set(s) created (Excluding files larger than 65,000 lines (xls) and 100++ (xlsx)) I still need to export each data set

into its own worksheet in one workbook.

Output will never exceed 65,000 lines or multiple sheets per book of 12.

Help is greatly appreciated

Signed New Jack...

*** DESIGN MACRO ***;

%macro Macro1(HHH);

PROC SQL;

   CREATE TABLE WORK.Q1_&HHH AS

   SELECT t1.*

      FROM WORK.QUERY_1 t1

      WHERE t1.Contract = "&HHH";

QUIT;

DATA WORK.Q2_&HHH;

  SET WORK.Q1_&HHH;

  IF Enrollment = . THEN Enrollment = 0;

  IF Enrollment1 = . THEN Enrollment1 = 0;

RUN;

PROC SQL;

   CREATE TABLE WORK.FINAL_&HHH AS

   SELECT t1.PRODUCT,

          t1.PlanType,

          t1.'Organization Name'n,

          t1.Contract,

          t1.'EG or DP'n,

          t1.PBP,

          t1.'Plan Name'n,

          t1.Service_Area,

          t1.County,

          t1.'Offers Part D'n,

          t1.Enrollment,

          t1.County1,

          t1.Enrollment1,

          (t1.Enrollment1 - t1.Enrollment) AS Change

FROM WORK.Q2_&HHH t1;

QUIT;

PROC DATASETS LIB=WORK NOLIST;

      DELETE Q1_&HHH  Q2_&HHH;

RUN;

%mend Macro1;

*** RUN MACRO ***;

%Macro1(H3359);

%Macro1(H3330);

%Macro1(H5549);

%Macro1(H3328);

%Macro1(H3361);

%Macro1(H3347);

%Macro1(H6181);

%Macro1(H3312);

%Macro1(H3370);

%Macro1(H3307);

%Macro1(H4866);

%Macro1(H5991);

%Macro1(H5528);


Accepted Solutions
Solution
‎03-21-2014 12:44 PM
Contributor
Posts: 59

Re: Exporting Multiple Data Sets to Multiple Worksheets in one Excel Workbook

Posted in reply to Dsrountree

%let pc   = XXXXXXXXXXXX;

%let folder = C:\Users\Temp;

data _null_;

dt=put( today( ),date9. );

call symput('dt',dt);

run;

proc export

     data    =  DATASET1

     outfile = "&folder.FILENAME_&dt..xls"

     dbms    =  excelcs  replace;

     sheet   = "EXCELSHEET1";

     SERVER  = "&pc";

run;

proc export

     data    =  DATASET2

     outfile = "&folder.FILENAME_&dt..xls"

     dbms    =  excelcs  replace;

     sheet   = "EXCELSHEET2";

     SERVER  = "&pc";

run;


Smiley Wink

View solution in original post


All Replies
Super User
Posts: 19,767

Re: Exporting Multiple Data Sets to Multiple Worksheets in one Excel Workbook

Posted in reply to Dsrountree

So what's your question?

SAS allows you to specify the sheet and file in each export. You can append a sheet to each file.

For example the following should create an excel file with 2 tabs, one for each dataset outputted.

libname output excel "path to my file.xlsx";

data output.h3359;

set h3359;

run;

data output.h3330;

set h3330;

run;

libname output;

Contributor
Posts: 59

Re: Exporting Multiple Data Sets to Multiple Worksheets in one Excel Workbook

The libname output excel produced the following: ERROR: The EXCEL engine cannot be found.

I normally use

PROC EXPORT DATA = output

File = "path/filename.xls"

DBMS = xlsreplace;

SHEET= "sheetname"

RUN;

This only works with one sheet per run.

Thanks

Super User
Posts: 19,767

Re: Exporting Multiple Data Sets to Multiple Worksheets in one Excel Workbook

Posted in reply to Dsrountree

Run your above code pointing to the same file but a different sheetname. What happens?

Contributor
Posts: 59

Re: Exporting Multiple Data Sets to Multiple Worksheets in one Excel Workbook

It creates a second file with the extension .BAK and the data corrupts and won't open

Super User
Posts: 19,767

Re: Exporting Multiple Data Sets to Multiple Worksheets in one Excel Workbook

Posted in reply to Dsrountree

Unfortunately I can't test because I'm on a much higher version. I suggest contacting Tech Support, link at the bottom.

I'm not sure they support 9.1.3 anymore, which I think is your equivalent version.

Solution
‎03-21-2014 12:44 PM
Contributor
Posts: 59

Re: Exporting Multiple Data Sets to Multiple Worksheets in one Excel Workbook

Posted in reply to Dsrountree

%let pc   = XXXXXXXXXXXX;

%let folder = C:\Users\Temp;

data _null_;

dt=put( today( ),date9. );

call symput('dt',dt);

run;

proc export

     data    =  DATASET1

     outfile = "&folder.FILENAME_&dt..xls"

     dbms    =  excelcs  replace;

     sheet   = "EXCELSHEET1";

     SERVER  = "&pc";

run;

proc export

     data    =  DATASET2

     outfile = "&folder.FILENAME_&dt..xls"

     dbms    =  excelcs  replace;

     sheet   = "EXCELSHEET2";

     SERVER  = "&pc";

run;


Smiley Wink

Occasional Contributor
Posts: 17

Re: Exporting Multiple Data Sets to Multiple Worksheets in one Excel Workbook

Posted in reply to Dsrountree

Hi Dsrountree. Thanks for the post about this issue. Could you help me about this? I tried, but I couldn't run.

My folder of excel is "D:\Vinculos_nuevos\SAS.xls" and the worksheet are "Table1","Table2" and "Table3"

The SAS table are "Sales","Revenue" and "Tax"

I need export "Sales" to "SAS.xls-Worksheet Table1"

                    "Revenue" to "SAS.xls-Worksheet Table2"

                    "Tax" to "SAS.xls-Worksheet Table3".

Thanks in advanced.

Regards,

Contributor
Posts: 59

Re: Exporting Multiple Data Sets to Multiple Worksheets in one Excel Workbook

Posted in reply to Francisco

This only works when &pc is linked to your individual PC name.


Occasional Contributor
Posts: 17

Re: Exporting Multiple Data Sets to Multiple Worksheets in one Excel Workbook

Posted in reply to Dsrountree

yes, are linked, but I couldn't run this code. How must be complete the code?

Thanks in advanced.

New Contributor
Posts: 2

Re: Exporting Multiple Data Sets to Multiple Worksheets in one Excel Workbook

Posted in reply to Francisco

This was just sample code. I've found a work around for now. Doesn't involve any programming in SAS, just setting up some macros in Excel to pull the data from the SAS exported Excel file into an Excel file already created that has the charts and graphs on other tabs.

Thanks for responding.

Contributor
Posts: 59

Re: Exporting Multiple Data Sets to Multiple Worksheets in one Excel Workbook

Posted in reply to danielleg

I hit a wall trying to find a work around - Sorry.

New Contributor
Posts: 2

Re: Exporting Multiple Data Sets to Multiple Worksheets in one Excel Workbook

Posted in reply to Dsrountree

hI also am working on something similar and hope someone can help. I am exporting data to an excel file, but in addition, I have other sheets in that file that I do not want to overwrite. basically, they are charts and graphs that will update from the updated data exporting from sas into excel.

i've tried using the following, but SAS doesn't like it when i use the outfile or excelcs options, and also i was having trouble with the server. i'm not quite sure what to put for my server name.

proc export

     data    =  DATASET1

     outfile = "&folder.FILENAME_&dt..xls"

     dbms    =  excelcs  replace;

     sheet   = "EXCELSHEET1";

     SERVER  = "&pc";

run;

just for some background. I'm running EG from a server 32 bit and have 32 bit Excel and have installed PC File Server.

i am able to use this code:

proc export

     data    =  TD

     file = "&folder.FILENAME_&dt..xlsx"

     dbms    =  xlsx  replace;

     sheet   = "EXCELSHEET1";

run;

But, the other two sheets in the file get wiped out and I don't want them to. Is there a way for me to prevent that from happening?

I've tried multipe ways taking out replace or using different sheeet names, but nothing seems to work.

Thank you for any help I can get!

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 12 replies
  • 15993 views
  • 1 like
  • 4 in conversation