What is the equivalent to grouping sets in SAS Proc Sql?

Question

I have a Teradata query that has a group by and then uses grouping sets to sum up the groups in a total line:

SELECT mygroup, count(x), sum(y)
FROM mytable
ORDER BY GROUPING (mygroup),mygroup
GROUP BY mygroup GROUPING SETS ((mygroup),())

I am now writing the same query in SAS EG as I want to create a scheduled report to be sent by email.

SAS EG does not have grouping sets as far as I can google.

My question is how I can create this "summary line" in SAS PROC SQL, or possibly by some other SAS EG magic?

Edit: Here is an example of the output I am looking for:

It is the total line I lack in SAS EG so far

Update: I have now written a Proc Tabulate and I am near my goal. The only field I cannot seem to get correct is the total percentage difference. In the table I have made a total line where I sum Number1 and 2, but for the percentage total I want a calculation of (Number1-Number2)/Number1 instead of a sum or other aggregate. Is this possible with tabulate, and how do I do it?

My current code:

PROC TABULATE
DATA=WORK.TEMP;

VAR Number1 Number2 percentageCalc ;
CLASS group /   ORDER=UNFORMATTED MISSING;
CLASS date /    ORDER=UNFORMATTED MISSING;
TABLE 
    /* ROW Statement */
    group*date
    all = 'Total'  ,
    /* COLUMN Statement */
    (Number1 * Sum={LABEL="Sum"} Number2 * Sum={LABEL="Sum"} percentageCalc  * Sum={LABEL="%"} )        ;
;

RUN;

Is there any way to do a manual calculation in my last total cell or can I somehow manually create the whole total line?

there are not `grouping sets` in proc sql I am afraid - Can you provide a full example to see if there are any workaround in terms of what you need to produce? — Altons, Jun 28 '16 at 07:11
Seems like a job for one of SAS's procedures -- `print`, `means` or `report`, according to the control you need on the output. `proc print` is pretty simple and can produce sub-totals. But I'd need to see a sample of the output you're looking for, as I'm not familiar with Teradata. — Dominic Comtois, Jun 28 '16 at 07:20
Thanks, I have added a table displaying what I am looking for. I will research proc print. — Stian, Jun 28 '16 at 07:26

score 1 · Accepted Answer · answered Jun 28 '16 at 08:18

1

I am not sure if I got a wrong end of the stick here, if I were you, I would do the grouping in PROC SQL (query wizard in SAS EG) to group the data and then I would use PROC TABULATE (Summary Table Wizard in SAS EG) to get what you want. You can also group and tabulate data in Summary Wizard in one go.

Here is the code:

data have; 
  input date $ number1 number2 ;
  datalines; 
  23.02.2016 2004 1951
  16.03.2016 14999 14670
  20.04.2016 20054 18999
 ;

PROC TABULATE
DATA=WORK.HAVE; 
    VAR number1 number2;
    CLASS date /    ORDER=UNFORMATTED MISSING;
    TABLE /* Row Dimension */
date 
ALL={LABEL="Total (ALL)"},
/* Column Dimension */
number1*
  Sum*
    ALL={LABEL="Total (ALL)"} 
number2*
  Sum*
    ALL={LABEL="Total (ALL)"}       ;
    ;

RUN;

View of Summary Table Task:

Here is the result:

answered Jun 28 '16 at 08:18

Vasilij Nevlev

1,449
9
22

Thanks, I will try this. Is best practise to create a table in work in the proc sql, or are there some better way of temporarily storing the data for use by the proc tabulate? – Stian Jun 28 '16 at 08:59
1

work is a default temporary library. Anything stored in there will get wiped when session ends. Usually, but not necessary, work library is on separate IO and on higher throughput drive like SSD because of the performance boost and its relatively small size, so usually it makes sense to use work for intermediary results only. – Vasilij Nevlev Jun 28 '16 at 09:12
In this post the WORK.HAVE dataset is created to show a complete program that others can run on their copy of SAS. For your problem you would replace references to WORK.HAVE in the PROC TABULATE code with the name of your real dataset. – Tom Jun 28 '16 at 10:59

What is the equivalent to grouping sets in SAS Proc Sql?

1 Answers1