to export a Proc Report export to excel with formats and into different tabs or sheets

[Santt0sh]

Hi All,

I am trying to create a SALES report using Proc Report for two categories of customers as of now (Corporates & Non-Corporates).

I am able to create the Datasets with all the calculations using Proc Reports, but now I need to Export this Proc Report to EXCEL(.XLSX) in two different sheets Corporates and Non-Corporates.

I am aware that this can be done using Proc Export and writing the data into two different Sheets in the Same Excel.

I'm trying to Keep the Format and the report as it's in Excel, and the Footnote of the report Should display the Tukey Fences. 

I have calculated the Upper Bound and Lower Bounds using proc Univarate and have created macro variables for the Upper Bounds and Lower Bounds.

Something like this:

Proc Univariate data = Sale012020 outtable = SaleJan20 Noprint;

Var Sales_Amount;

Run;

/*UPPER NAD LOWER BND CALCULATIONs*/

DATA SaleJan20 ;

Set SaleJan20 ;

  lower = _Q1_ - (1.5*_QRANGE_); /*_QRANGE_ = Interquartile Range*/

UPPER= _Q3_+ (1.5*_QRANGE_) /*_QRANGE_ = Interquartile Range*/

Run;

Data _NULL_;

Set SaleJan20 ;

Call Symput('UPPER',UPPER);

CALL SYMPUT('LOWER',LOWER);

RUN;

The Format for the UPPER and LOWER needs to percent8.2 When I tried to apply the Percent to the Macro Variables on the Report it is Displayed as 1.6E %(For E.g.)

I have tried using the ODS Exceltagsets and ODS excel File = options();

I am able to write the Output for (for e.g. Corporates ) to a sheet in the excel workbook, but not able to retain the formats and write the output of ( Non-Corporates) in another sheet.

Could you please guide or Suggest this?

Please find the attached Sales input and report in excel attached.

Kindly note that I have created the Tukey Fences using Excel formulas and the Sales Amounts or Sale numbers are fictitious.

Regards,

[Santt0sh]

I have used ODS TAGSETS.EXCELXP FILE="path " Style= analysis;
ODS TAGSETS.EXCELXP FILE options (Sheet_Name = "CORPORATES" AUTOFILTER ='YES');
Proc Report data = SaleJan20 ;
STAEMENTS -------;
RUN;
ODS TAGSETS.EXCELXP CLOSE;
ODS LISTING;


























);

[PaigeMiller]

ODS EXCEL should allow the formats you choose to be maintained; and also allows different Excel tabs.

Without having your data and your actual working SAS PROC REPORT code, it's really hard to be more specific. (Most of us will not download your Excel file, as MS Office files can be a security risk). 

Here is an example:

proc sort data=sashelp.class out=class;
    by sex;
run;

ods excel file="test.xlsx" options(sheet_interval='bygroup');
proc report data=class;
    by sex;
    columns name age height weight ratio;
    define name/display;
    define age/display;
     define weight/display;
    define height/display;
    define ratio/computed format=percent8.2;
    compute ratio;
        ratio=height/weight;
    endcompute;
run;
ods excel close;
     

[Santt0sh]

Hi,

Thank you for your response!!
I tried your suggestion but I have a huge data set h nice have split the table into Corporate and Non Corporates
And I’m using a macro to do the calculations, and generate reports for each separately.
If I keep sorting the dataset it might take long and the calculations also are different for each departments.

I’m trying to export the report generated by the Proc report to an excel sheet : sales 2020.xlsx which will have data for sales for each month for corporate and non corporates.
The calculations will be done in the SAS datasets.

Kindly suggest

[PaigeMiller]

As I said earlier, we can't be more specific unless you show us a portion of your data (as SAS data step code which you create manually, or via these instructions, and not in any other format).

As I said earlier, we also need to see your actual working PROC REPORT code.

[Tom]

Is your question about how to create the report you want?

Or how to write that report to an XLSX file.

For the second just use the ODS EXCEL statement around your code that produces the report(s).

So assuming you have two PROC REPORT steps that you need to run to produce the two different reports just add an ODS EXCEL statement in between to change the sheet name.

ods excel file='myfile.xlsx';
ods excel options (sheet_name='CORPORATE');

proc report data=have;
  where TYPE='CORPORATE';
....
run;

ods excel options (sheet_name='NON CORPORATE');

proc report data=have;
  where TYPE='NON-CORPORATE';
....
run;
ods excel close;

[Santt0sh]

Hi Tom,

Thank you for your response.
Let me try to explain:
I have datasets for the last one year and I am trying to create table for corporate and no corporates and calculate as per the requirements for both the sections.
Even if create the report as suggested by you for each moth by using where clause.
Again I need to consolidate the report for the last 12 months.
So I’m splitting the datasets and trying to create the report.

Let me try as you have suggested.

Thanks

[Tom]

---

@Santt0sh wrote:
Hi Tom,

Thank you for your response.
Let me try to explain:
I have datasets for the last one year and I am trying to create table for corporate and no corporates and calculate as per the requirements for both the sections.
Even if create the report as suggested by you for each moth by using where clause.
Again I need to consolidate the report for the last 12 months.
So I’m splitting the datasets and trying to create the report.

Let me try as you have suggested.

Thanks

---

You seem to be confusing the process of making report with the concept of writing something to Excel.

If you want to produce a summary report you can figure out the SAS code to produce the report. Once you have that designed then add the ODS statements to route the report to EXCEL.

You probably do not want to include all of the raw data in the EXCEL file.  Just the report.

[Santt0sh]

Hi Tom,

Yes I understand what you are suggesting. I am not dumping all the data to the excel I am only creating a report using Proc report and trying to output the report as it is to Excel sheet.

I’m not able to write the report to different sheets.
The ods excel is replace the sheet.
Kindly suggest

Thanks

[Tom · Accepted solution]

Are you not expecting PROC REPORT to create a NEW sheet?

You cannot use ODS EXCEL to write PART of an worksheet.  Use it to produce the whole worksheet.

Try this example:

ods excel file='c:\downloads\class.xlsx' ;
ods excel options (sheet_name='Female');
proc report data=class;
  where sex='F';
  column name age height weight;
  define name / group;
run;
ods excel options (sheet_name='Male');
proc report data=class;
  where sex='M';
  column name age height weight;
  define name / group;
run;
ods excel close;

[Santt0sh]

Hi,

Yes I was expecting to create a new sheet as in Proc export.
I don’t want to calculate the Tukey Fence manually as this will be a Monthly job in future.

Thanks

[Tom]

---

@Santt0sh wrote:
Hi,

Yes I was expecting to create a new sheet as in Proc export.
I don’t want to calculate the Tukey Fence manually as this will be a Monthly job in future.

Thanks

---

So that is a different question that has nothing to do with whether or not you write the report to EXCEL.

Do you know how to calculate that?  What code are you running now to calculate it?

[Santt0sh]

Hi Tom,

Thank you for your suggestion!.
It worked for me, i had make few changes to sas code used for calculations.

Regards

[DrAbhijeetSafai]

Very interesting! Thanks. 

- Dr. Abhijeet Safai