conditional subtotal proc report

[NazaninSAS]

hi,

I can generate the first table using proc report. However, I want to generate the second table. more specifically, the highlighted addition.

Highlighted addition is the subtotal of CMAs under EEOG 7.

How can I do it in proc report?

Thanks,

Nazanin

[ballardw]

---

@NazaninSAS wrote:

hi,

I can generate the first table using proc report. However, I want to generate the second table. more specifically, the highlighted addition.

Highlighted addition is the subtotal of CMAs under EEOG 7.

How can I do it in proc report?

Thanks,

Nazanin

---

Do the items have to be exactly in that order?

If so you may have to summarize your data and use proc print. The subtotal features of Proc Report using the RBREAK statement would ant to group the data differently by the CMA so that all of one CMA are together so the subtotal could appear either before or after the common CMA values.

Trying to have mixed orders of a single variable within Proc Report is not a trivial task when combined with grouping and subgroup summaries.

The good news is that proc summary can likely do the summaries with the correct class statement with the missing option and likely a data step to add a variable to control order. Then sort by that variable and use proc print to display.

[NazaninSAS]

I use "rbreak" and "break before" for summing up the EEOG, but in this particular case, in addition to rbreak and break,  I want to sum the CMA under EEOG 7. I don't want the sum of CMAs for other EEOGs.

tnx

Nazanin

[ballardw]

Proc report is likely not the tool to create the second table.

The reason is that the way Proc Report builds tables that the summaries will not create subgroup totals the way you show them.

You can get the summaries you want but you will have to do a little work.

Please examine this code and the output data set:

proc summary data=sashelp.class;
   class sex age;
   var height weight;
   output out=work.classsum mean=;
run;

There is a variable in the output data set work.classsum named _TYPE_ that SAS provides to indicate which summary a line comes from. _Type_=0 is an overall summary, _type_=1 is the summary for AGE only, _type_=2 is the summary for Sex only, and _type_=3 indicates the summary for the combinations of Sex and Age variable groups.

If I wanted to display the Age only group above the Sex and Age, which is very similar to what your table 2 does, I could use the _type_ variable to filter the records.

proc print data=work.classsum noobs;
   where _type_ in (1,3);
   var sex age height weight;
run;

With more class variables you get different summaries to consider, select and print.

A side effect of this, if you can make table 2 the exact same code removing one value of _type_ with create table one. Your statistic would be sum instead of the mean I used above.

[NazaninSAS]

basically, the second table, What I am looking for. I can produce the first table.

Thanks,

Nazanin