BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
Santt0sh
Lapis Lazuli | Level 10

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,

 

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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;

Screenshot 2022-04-05 164827.jpg

View solution in original post

13 REPLIES 13
Santt0sh
Lapis Lazuli | Level 10
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
Diamond | Level 26

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;
     

 

 

 

--
Paige Miller
Santt0sh
Lapis Lazuli | Level 10
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
Diamond | Level 26

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.

--
Paige Miller
Tom
Super User Tom
Super User

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
Lapis Lazuli | Level 10
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
Super User Tom
Super User

@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
Lapis Lazuli | Level 10
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
Super User Tom
Super User

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;

Screenshot 2022-04-05 164827.jpg

Santt0sh
Lapis Lazuli | Level 10
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
Super User Tom
Super User

@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
Lapis Lazuli | Level 10
Hi Tom,

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

Regards
DrAbhijeetSafai
Pyrite | Level 9

Very interesting! Thanks. 

 

- Dr. Abhijeet Safai

Dr. Abhijeet Safai
Certified Base and Clinical SAS Programmer
Associate Data Analyst
Actu-Real

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 13 replies
  • 7875 views
  • 4 likes
  • 4 in conversation