topic Re: Creating an Excel file in SAS Studio

Creating an Excel file

kajal_30 — Mon, 13 May 2019 13:51:22 GMT

I am trying creating an excel file with three tabs after an extract transformation.Can anyone help me with the transformation.

Thanks

Kajal

Re: Creating an Excel file

Reeza — Mon, 13 May 2019 15:48:49 GMT

ODS EXCEL or PROC EXPORT are the methods for creating an Excel file. Both have examples in the documentation.

Vague questions get vague answers, if you need more assistance please provide more details.

@kajal_30 wrote:

Hi

I am trying creating an excel file with three tabs after an extract transformation.Can anyone help me with the transformation.

Thanks

Kajal

Re: Creating an Excel file

kajal_30 — Mon, 13 May 2019 18:25:32 GMT

I am creating a file

ods EXCEL FILE = "/user/kajal30/report.xlsx"
options (sheet_interval = 'bygroup'
sheet_label = 'origin');
proc report data=sashelp.cars;
by make;
run;

Here I need an excel sheet with multiple tabs ( each tab for diff make ) and name them as year_report_for_jan2011_honda, year_report_for_jan2011_acura, year_report_for_jan2011_tesla ........ and so on.

Hope this helps

Thanks

Re: Creating an Excel file

Reeza — Mon, 13 May 2019 18:56:13 GMT

First example in the documentation covers this:

https://documentation.sas.com/?docsetId=odsug&docsetTarget=p09n5pw9ol0897n1qe04zeur27rv.htm&docsetVersion=9.4&locale=en

Specifically for your issue - I think your name is too long so SAS ends up truncating it - limit on worksheet name in Excel is 30 characters. The examples you've shown would fit 30 but not all would.


ods EXCEL FILE = "/home/xxxxxx/Demo1/report.xlsx"
options(sheet_interval="bygroup"
           suppress_bylines="yes"
           sheet_label="Report for Jan 2011"
           embedded_titles="yes"
           embed_titles_once="yes" );


proc report data=sashelp.cars;
by make;
run;

ods excel close;

This gets you pretty close.

@kajal_30 wrote:

I am creating a file

ods EXCEL FILE = "/user/kajal30/report.xlsx"
options (sheet_interval = 'bygroup'
sheet_label = 'origin');
proc report data=sashelp.cars;
by make;
run;

Here I need an excel sheet with multiple tabs ( each tab for diff make ) and name them as year_report_for_jan2011_honda, year_report_for_jan2011_acura, year_report_for_jan2011_tesla ........ and so on.

Hope this helps

Thanks

Re: Creating an Excel file

kajal_30 — Fri, 17 May 2019 12:52:40 GMT

Thank you so much