topic Re: Creating single report from mutiple excels in New SAS User

Creating single report from mutiple excels

samuelsuresh — Tue, 19 Feb 2019 16:30:19 GMT

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.

Re: Creating single report from mutiple excels

PaigeMiller — Tue, 19 Feb 2019 16:54:24 GMT

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.

Re: Creating single report from mutiple excels

samuelsuresh — Tue, 19 Feb 2019 16:51:28 GMT

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

Re: Creating single report from mutiple excels

PaigeMiller — Tue, 19 Feb 2019 16:55:53 GMT

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.

Re: Creating single report from mutiple excels

samuelsuresh — Tue, 19 Feb 2019 16:58:41 GMT

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

Re: Creating single report from mutiple excels

Cynthia_sas — Tue, 19 Feb 2019 17:12:29 GMT

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

Re: Creating single report from mutiple excels

samuelsuresh — Thu, 21 Feb 2019 04:20:54 GMT

Thank you Cynthia