## Summary Report

Solved
Occasional Contributor
Posts: 7

# Summary Report

I’m having issues getting report to come out as desired. Below is a snippet of what the data looks like then below it is a snapshot of what I’d like the report to look like. It seemed to me that it would be simple, but I can’t seem to get it to group by state or to figure out how to count the number of observations by state. Any help would literally make my week. I basically need it to group by everything, but separated by state.

I've looked up tons on proc freq, summary, sql, print, etc. Help?

Accepted Solutions
Solution
‎01-18-2017 09:57 AM
Occasional Contributor
Posts: 7

## Re: Summary Report

Here's the code I ended up using, in case it's ever useful to someone:

proc tabulate data=*** format=9.;

class state amtid;

var amount percent number_of_payments;

table state*(amtid all='Total'),

N amount percent*mean number_of_payments*mean=' ';

run;

All Replies
Super User
Posts: 13,361

## Re: Summary Report

If you provide data in the form of a data step (not a picture, most of us do not have the time to enter data from sratch) then we can test code. Instructions here will show how to create datastep code from a SAS data set: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...

What would N_state be for the shown example input data?

In the final report is Amount the max, min, mean of the amount within the State and amtid combination?

Same question for Percent and N_Payments

And show the values of the SUMs for the total row.

Occasional Contributor
Posts: 7

## Re: Summary Report

[ Edited ]

The original dataset is very large...so I'm sitting here waiting for that macro to run. In the meantime:

N_State would be the count for that state within that AMTID. The Amount column is a set amount that correlates to both amtid and number of payments. I.e. no amtid has more than one number of payments or amount. This goes for percent as well. As far as the last row for total goes, I'm looking most importantly to sum the occurences of state within that amtid and to sum "amount."

For what I gave you, sum of state would be 5,  amount would be 14800, and percent 26.99.

Super User
Posts: 13,361

## Re: Summary Report

If your original data is large you may want to make an example set:

data example;

set mylarge data (obs=50);

run;

and then create the data step from that set.

SAS Super FREQ
Posts: 9,329

## Re: Summary Report

Hi,
I'm not sure about what you show above. For the SC rows only, I get 7,400 not 14,800:
1800+1800+1400+1000+1400 = 7,400

Wouldn't the sum for SC be only the SC rows?

Doing some desk checking. It looks like a PROC TABULATE output or a PROC REPORT output to me. But without a test dataset, no one is likely to make data and write a report program, too.

cynthia
Occasional Contributor
Posts: 7

## Re: Summary Report

Thanks, I completely understand, just wasn't able to get the data in a
workable format today and it's also sensitive so I'd have to fake it.
Anyway, I figured it out with proc tabulate. I wasn't writing my table
statement correctly.

attachments. -##
Solution
‎01-18-2017 09:57 AM
Occasional Contributor
Posts: 7

## Re: Summary Report

Here's the code I ended up using, in case it's ever useful to someone:

proc tabulate data=*** format=9.;

class state amtid;

var amount percent number_of_payments;

table state*(amtid all='Total'),

N amount percent*mean number_of_payments*mean=' ';

run;

☑ This topic is solved.