Proc Report count of a unique row id =1

[Jacob]

Hi all,

I have a summary report that I am trying to build that groups together Policy Exceptions and then counts the number of request with that exception, the total number of exceptions occuring (because a single request may have multiple exceptions) and the sum of the request amounts for each Exception.  I have a column that I created in my proc sql that creates a unique row id so the first time the request number appears it shows as 1 and for each subsequent exception it would increase (i.e. 2, 3,etc...)  I would like to do a count of the nunmber of requests for each exception where the unique_row_id =1.  Sample data is below.

Unique_Row_id	Req_No	Product	Exception	Request_Amt	Trans_Type
1	308257	Real Estate	Insufficient DSC	633239.49	Existing Money
2	308257	Real Estate	LTV > 75%	633239.49	Existing Money
1	308475	Real Estate	Insufficient DSC	280500.00	New Money
2	308475	Real Estate	LTV > 75%	280500.00	New Money
3	308475	Real Estate	Other	280500.00	New Money
1	308667	Real Estate	Insufficient DSC	238000.00	Existing Money
1	308668	Real Estate	Insufficient DSC	192750.00	Existing Money
1	308669	Real Estate	Insufficient DSC	256000.00	Existing Money

This is how i would like the final report format to be:

Policy Exception	# of Requests	# of Exceptions	$ of Exceptiosn
New Money
Insff. DSC	1	2	$305,500.00
LTV > 75% INV Office	1	1	$280,500.00
New Money Total:	2	3	586,000.00
Existing Money
Debt to income > 60%	2	3	$73,691.21
Insuff. DSC	6	8	$3,982,160.72
Existing Money Total:	8	11	$4,088,851.93
Grand Total:	10	14	$4,641,851.93

Any help is greatly appreciated.

[Reeza · Accepted solution]

What about a SQL Step and then proc print or report to get your totals/display the output:

proc sql;

create table want as

select trans_type, count(distinct req_no) as num_requests, count(*) as num_exceptions, sum(request_amt) as amount_exception

from have

group by trans_type, exception;

quit;

See the SAS examples in proc print and proc report on how to get your totals displayed properly.

PS. It helps if your sample data and results line up, ie the data produces the table.

[Jacob]

Reeza, Thank you.  This did the trick.  Sorry for the disconnect in the sample data and the sample output...I'll keep that in mind for the next time.