Creating single report from mutiple excels

[samuelsuresh]

I have three macros below which creates three excel reports with multiple sheets inside.

**12 months**

%metrics(score =y,vintage=y,excel=excel1);

**06 months**

%metrics(score =y,vintage=y,excel=excel2)

**18 months**

%metrics(score =y,vintage=y,excel=excel3)

when i run this SAS it creates three excel sheets with multiple sheets in   winscp location sas/output 

I want to know how to create a single excel picking up particular sheets from each three excel and output it into new excel document below these three macros and output it in same winscp location.

[PaigeMiller]

ods excel file="\myserver\myfolder\myexcelfile.xlsx" options(sheet_name='SheetName1');
... some SAS code ...

ods excel options(sheet_name='SheetName2');
... some SAS code ...

ods excel options(sheet_name='SheetName3');
... some SAS code ...

ods excel close;

Above is the general approach. Of course, we don't know what your macro %METRICS does, so there may be other approaches. 

[samuelsuresh]

Thanks Miller. Can you explain how it reads multiple excels and create one excel file.

[PaigeMiller]

It doesn't read multiple Excel sheets. You don't create multiple Excel files. You run the code above to create one Excel file with multiple worksheets.

[samuelsuresh]

Thanks Miller. But the macros are creating in a way to create multiple Excel which I cannot change now. So i need to know how to do it when there are multiple excel sheets

[Cynthia_sas]

Hi:
It's still not clear what you need to do. No one can give you good advice on this because the solution depends on what is INSIDE the %metrics macro program definition. If you already have ODS EXCEL statements inside that macro program, then adding more ODS EXCEL statement outside the macro calls won't work.

I would recommend going back to the person who wrote the %metrics macro program definition and ask them for help with your new requirements or, open a track with Tech Support where they can look at ALL your code including ALL the macro program code and help you with a resolution.

Cynthia

[samuelsuresh]

Thank you Cynthia