Help with PROC REPORT to display summarized fields in ROWS vs COLUMNS

[ramkal21]

Hello all,

Input to my SAS program

Organization (ORG_ID), Employee ID (EMPL_ID), Employee Salary (EMPL_SAL), Employee Bonus (EMPL_BONUS)

ABC, 12345, 5000, 1000

ABC, 78899, 7000, 2000

DEF, 67890, 10000, 3000

DEF, 22993, 8000, 500

NOTE: ORG_ID, EMPL_ID, EMPL_SAL and EMPL_BONUS are SAS variables

Output report am desiring is

TITLE on top of the report along with PAGE numbers in the report.

Followed by Summary of amounts based on each field in the SAS dataset / input file and a final set of TOTALs  for each of those fields as well at Organization level

ORG_ID

--------------

ABC                    EMPL_SAL                  12,000

                             EMPL_BONUS            3,000

DEF                     EMPL_SAL                  18,000

                             EMPL_BONUS            3,500

TOTAL                EMPL_SAL                 30,000

                             EMPL_BONUS             6,500

With PROC REPORT or PROC Summary i was not sure how i can customize the fields to display like above, i can display them as columns but not as rows.

Could you please suggest any examples which i could use to achieve the above outcome?

Thanks,

Ramanujam

[ballardw]

Proc report really doesn't like multiple variables in a single column.

Proc tabulate however seems to do what you request:

data have;
  infile datalines dlm=',';
  input ORG_ID $ EMPL_ID EMPL_SAL EMPL_BONUS;
datalines;
ABC, 12345, 5000, 1000
ABC, 78899, 7000, 2000
DEF, 67890, 10000, 3000
DEF, 22993, 8000, 500
;


proc tabulate data=have;
   class org_id;
   var empl_sal empl_bonus;
   tables (org_id all='All org total')*(empl_sal empl_bonus),
          Sum
   ;
run;

Details like Labels and formats can be discussed if this works.

[ramkal21]

Thanks a lot @ballardw. This worked and exactly what i was looking for.

Could you please let me know whether below additional formatting is possible?

- option to remove the Tabular borders?

- option to make it as a report with 'Page' numbers

- make the whole output 'left' indented. i wrote the output to a saslist file and it came up aligned 'center'

- also if you could point me to examples to apply styling to ORG-ID - like i would like it to be underlined, that would be helpful.

ORG_ID
--------

- to remove the 'Sum' column label. would not need it to show up in my scenario

--------------
| Sum |
+------------|
| |
| |
| 12000.00|
+------------|

Thank you so much. You have saved a lot of development hours for me already.

Regards,

Ramanujam

[Cynthia_sas]

Hi:

  I would recommend that you investigate previous postings that discuss the use of ODS HTML, ODS RTF and ODS PDF destinations. The LISTING destination does not support much cosmetic formatting for output.

  ODS will provide you with much more capability to created reports for a browser, Word, Acrobat or even Excel (ODS EXCEL).

  There have been many previous postings about the use of ODS to get better looking output.

Cynthia

[ramkal21]

Thank you @Cynthia_sas  for your response. Could you let me know if ODS will work in z/OS Mainframe?

Also, my query on formatting was around PROC TABULATE command if those options are possible?

Thanks,

Ramanujam

[ballardw]

Most of the appearance controls are depending on ODS destination. If by SAS list file you mean the ODS LISTING destination you lose a lot of control options.

To maintain appearance of Listing output, open a text box using the </> icon above the message window and paste the copied text there.

In the listing destination the "lines" are built by printing characters based on position in the FORMCHAR option string on the Proc statement.

Example:

ods listing;
/* default 
options formchar="|----|+|---+=|-/\<>*";
*/
                     
proc tabulate data=have style=journal
    formchar="                    "
;
   class org_id;
   var empl_sal empl_bonus;
   tables (org_id all='All org total')*(empl_sal empl_bonus),
          Sum
   ;
run;

You can modify or suppress default labels by using ="label" after the statistic. and empty string ='' (that is two single quotes) suppresses.

proc tabulate data=have style=journal;
   class org_id;
   var empl_sal empl_bonus;
   tables (org_id all='All org total')*(empl_sal empl_bonus),
          Sum=''
   ;
run;

Underline in Listing I don't have clue.

[ramkal21]

Thank you @ballardw  for your inputs. It helped.

One thing though below field setting it up as spaces. It ended up adding a new line between the fields values, which i really dont need. Is there a way to remove that extra line or based on my understanding so far, it is not possible?

formchar="                    "

Also, could you please elaborate on what you had meant by below line?

"To maintain appearance of Listing output, open a text box using the </> icon above the message window and paste the copied text there."

Also, by SAS List i meant the below DD Statement (in Mainframe), the output of PROC TABULATE is automatically sent to below DD statement by SAS.

//SASLIST DD DSN=<HLQ>.SASLIST,

Thanks again for your inputs!

Ramanujam

[ramkal21]

@ballardw to clarify on my response on 'FORMCHAR' change, am getting a new line space between EMPL_SAL row and EMPL_BONUS. My recent query if it is possible to eliminate this?

[ballardw]

You have to show the exact code you are running and if the data is different than the set I created then that as well.

Basically that "space" was always there. It was just occupied by the line that separated the cells. Using the FORMCHAR option just replaces those line making characters with spaces (or a different character). It will still take the same amount of vertical space.

As @Cynthia_sas said, the LISTING destination has very few options for actual control of appearance.

Try using destination like ODS HTML and specify the JOURNAL style to suppress cell borders.

[Cynthia_sas]

Hi: @ballardw is correct, PROC TABULATE only allows the divider characters to be replaced with spaces. This is where creating ODS output would help you. And, yes, ODS HTML, ODS RTF and ODS PDF will work on z/OS -- you'll need to possibly write your result files to a disk where you can download the results to your local PC because Word and Acrobat don't exist in the mainframe environment to open and render files. As for ODS HTML, I think you'll either have to download that file too or write the ODS file to a web server so you can open the file from there. You might find some user group papers on using ODS on the mainframe. Or you might want to work with SAS Tech Support or look in the SAS Companion for z/OS for more information. As I remember, you can either write to a PDS member with your output, or you can write to a sequential file. I always wrote to a single sequential file because those files were easier to download than downloading PDS members from the mainframe. I do remember changing the LRECL for the output fileref on my DD statement (using JCL). My mainframe days are back in the SAS 8 time frame, so I am very rusty.

  You may find some of the tips in this document to be useful: [PDF] Tips for Using ODS in the Z/OS Environment - Free Download PDF (silo.tips) .
Cynthia

[ramkal21]

Thank you @Cynthia_sas and @ballardw. I was not able to access to silo.tips link due to an internal security issue. Could you please share any examples of ODS HTML that i could refer to, to start with?

Actually, i need to use the file / report output from ODS in the Mainframe and send it to a report repository location. Though, i should still be able to view the report clearly on Mainframe as well. I will explore more on this.

Thanks!

[ramkal21]

Thanks @Cynthia_sas. I need to send the report to a Mainframe file and not to a windows / PC. so could you please let me know if there is any option at all within ODS listing to configure the cell width or any other ODS styles that can still be in readable format on Mainfraime side?

Thanks for your inputs.

[ballardw]

---

@ramkal21 wrote:

Thanks @Cynthia_sas. I need to send the report to a Mainframe file and not to a windows / PC. so could you please let me know if there is any option at all within ODS listing to configure the cell width or any other ODS styles that can still be in readable format on Mainfraime side?

Thanks for your inputs.

---

This is starting to sound like a very specific file structure is required and you have not provided a clear description.

I suspect that the approach you may need, given the no ODS / HTML is to summarize the data, possibly using Proc tabulate or not and then use the data step PUT statements to put each column of values at specific locations to a text file. That would result in no spaces between "rows" and you can specify any column start position you want.

This is very old school but still viable.

HOWEVER this also requires actual example data, the values can be different than yours but some values are needed in the form of a data step and a very clear description of the summaries and file output needed.

Step one is to get the data summary and structured in such a way that the "breaks" or what ever you intended.

Note that this would require enough information to calculate page numbers, such as how many rows go on a given "page" of the file and how a "page" is indicated in the file as the automatic "page number" tools are all ODS based.

[Tom]

---

@ramkal21 wrote:

@ballardw to clarify on my response on 'FORMCHAR' change, am getting a new line space between EMPL_SAL row and EMPL_BONUS. My recent query if it is possible to eliminate this?

---

Just make your own reporting dataset and then PRINT it.

Yours looks trivial.  You seem to have THREE variables in the report.  

data report;
  input name :$32. label :$200. value;
cards;
ABC EMPL_SAL 12000
ABC EMPL_BOUNS 3000
;

proc print data=report n0obs;
  id name;
  var label value;
run;

Result:

The SAS System                                                                                  18:20 Thursday, November 9, 2023  10

name      label       value

ABC     EMPL_SAL      12000
ABC     EMPL_BOUNS     3000