create report from proc report

[AlexeyS]

Hi, 

I have to create the following report. I have attached excel file.

From my table

data sales;
format date ddmmyy10.;
date="22may2022"d; store="A"; Sales=50; Quantity=60; Expenses=40; output;
date="23may2022"d; store="A"; Sales=60; Quantity=70; Expenses=40; output;
date="22may2022"d; store="B"; Sales=10; Quantity=60; Expenses=30; output;
date="22may2022"d; store="B"; Sales=20; Quantity=70; Expenses=30; output;
RUN;

Excuse me for many questions. I don't know how can i merge cells like "Total Parameters"(C1:D1) or "Total"(A5:B5) , how can i add subtotal and totals for each store for specific variables, how can i add formulas, for example : Contrib = Sales- Expenses (C5-E5), or in Total/Summary, how can  add a bold border between each store and of courses how can i add a colors.

Thank you

[Cynthia_sas]

Hi:

  You did provide some sample data, but did not show the code you've tried. With PROC REPORT, you can easily do spanning headers in the column statements. It looks to me like your order is actually by store order first (all the store A before the store B) and then by date within each store. That requires an extra helper variable in PROC REPORT and since your DATE variable is numeric, it is harder to insert the string TOTAL at the break, so that also required another helper variable. In the code below, when creating the sample data, I made CHARDATE as a character version of the formatted date and I made STOREDUP as a copy of the STORE value.

  Having this helper variables allowed me to have STORE as the primary ordering variable and then have DATE as the secondary ordering variable, and then, using NOPRINT allowed them to control ordering, but I didn't have to see them on the report row. Then I could still display CHARDATE and STOREDUP in the order you wanted on the report row.

  Using the BREAK AFTER STORE / SUMMARIZE statement told PROC REPORT to insert the subtotal line and the COMPUTE AFTER STORE compute block allowed the string 'Total' to be assigned to the CHARDATE column on the subtotal line. Same thing with the string 'Summary' and the RBREAK AFTER / SUMMARIZE statement and the COMPUTE AFTER compute block for the Grand Total. Formatting for the breaks (colors, etc) was done using style overrides.

  Calculating CONTRIB was also done with a COMPUTE block. I did not understand why the report you showed does not have any values in all the rows. PROC REPORT will want to generate a value for CONTRIB on every row, so I just took the default.

  PROC REPORT does not allow you to merge cells as you can in EXCEL, so the word Total is placed in the CHARDATE column and the word Summary is placed in the CHARDATE column and you can't merge that column on the summary line with the cell at the bottom of the STORE column.

  You could try to fiddle with the border lines on the summary row, by altering the right border for CHARDATE and the left border for STORE to be the same color value. But that is a bit fiddly. There have been some previous postings on the forums that show how to do this. However, there are enough complex things in this example that I didn't bother with more border fiddling other than the bottom border, which applied to the subtotal row.

  Hope this gets you started in the right direction.

Cynthia

[AlexeyS]

First of all i want to thank you. Now i understand how the proc report works.

I still have a few questions to you.

1. Can i in the Expenses and Contrib columns keep only values for total and Summary levels?

2. Can i add in A1 cell the date the report was produces?

3. How can i insert the blank line after Total lines with white background for all columns.

I tried the following, but it only added a line but the colors was not white, i tried to change it but id didin't work

compute after store / summarize;
chardate='Total';
call define ...
call define...
line '';
call define(_cols_,'style','style=Header{background=white}');
endcomp;

4. Can i add formulas like in Excel in specific cell?

Thank you very much

[Cynthia_sas]

Hi:
It seems like you are still thinking "like Excel" because you're asking questions from an Excel viewpoint. What do you mean by "A1"? That is Excel notation. I'd recommend that you try to think in terms of what columns in PROC REPORT produce what you see when you finally open the output in Excel. For example:

Column A2 and columns B2 in Excel are created from the PROC REPORT column statement. The word "Date" is in A2 and the word "Store" is in B2. The cell above them is what I think you're calling A1. I would call it the merged cell that PROC REPORT puts above those 2 columns and above the last 2 columns because there is a spanning header text of Sales Parameters above the Sales and Quantity columns. When PROC REPORT decides how to write the header rows, it see that there is only a spanning header above Sales and Quantity. so the first report row has blanks above Date and Store and blanks above Expenses and Contrib.

  So the way you would put anything above the Date header and the Store header is to change the syntax in the column statement using a spanning header above those 2 columns to something like:

regarding the blank line -- that should be a simple compute block change to include a LINE statement and since you wanted the line to be white, I used a style override for that:

  Regarding your question #1 -- yes, you could blank out those values and the easiest way to do it would be to make the background color and the text color both white, which would essentially "disappear" the number, but still allow it to be used for the subtotal and the grand total:

  To me, this seems a very bad idea (to blank out the values). I have worked for different managers at different companies who would insist on seeing every number, EVERY number and they would open up their own Excel or Quattro or their phone and tally up the numbers themselves to be sure that my program "did it right". So I would never do what you're asking. There are other ways to do it with PROC REPORT, but most of them require either temporary variables or more complicated hidden columns.

  Regarding your question #4, yes, there is a way to add formulas. I rarely do it however. For one reason, there has always been, for my reports, a need for Excel ... and. I want Excel and I want it in Word. I want Excel and make me a PowerPoint with the table. I want Excel and I need to have it as a separate CSV file. I want Excel and I want a PDF file. The formula you code would ONLY work in the ODS EXCEL destination. You would render that column useless in other destinations.

  The documentation for ODS EXCEL has examples of formulas and you may also have to turn on the FORMULAS= option if you want cells with an = sign to be treated as formulas.This user group paper https://support.sas.com/resources/papers/proceedings16/SAS5642-2016.pdf is explaining the difference between using ODS EXCEL (what you're using) and ODS TAGSETS.EXCELXP (an older destination before the XLSX specification from Microsoft). There is a discussion of formulas for ODS EXCEL in this paper.

  To learn more about the COMPUTE block in PROC REPORT, here's a paper to get you started: https://support.sas.com/resources/papers/proceedings17/SAS0431-2017.pdf .

Cynthia