The SAS Output Delivery System and reporting techniques

Proc Report count of a unique row id =1

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 9
Accepted Solution

Proc Report count of a unique row id =1

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_idReq_NoProductExceptionRequest_AmtTrans_Type

1

308257

Real EstateInsufficient DSC633239.49Existing Money
2308257Real EstateLTV > 75%633239.49Existing Money
1308475Real EstateInsufficient DSC280500.00New Money
2308475Real EstateLTV > 75%280500.00New Money
3308475Real EstateOther280500.00New Money
1308667Real EstateInsufficient DSC238000.00Existing Money
1308668Real EstateInsufficient DSC192750.00Existing Money
1308669Real EstateInsufficient DSC256000.00Existing Money

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

Policy Exception# of Requests# of Exceptions$ of Exceptiosn
New Money
Insff. DSC12$305,500.00
LTV > 75% INV Office11$280,500.00
New Money Total:23586,000.00
Existing Money
Debt to income > 60%23$73,691.21
Insuff. DSC68$3,982,160.72
Existing Money Total:811$4,088,851.93
Grand Total:1014$4,641,851.93

Any help is greatly appreciated.


Accepted Solutions
Solution
‎01-30-2012 05:19 PM
Super User
Posts: 19,789

Proc Report count of a unique row id =1

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.

View solution in original post


All Replies
Solution
‎01-30-2012 05:19 PM
Super User
Posts: 19,789

Proc Report count of a unique row id =1

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.

Occasional Contributor
Posts: 9

Proc Report count of a unique row id =1

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. 

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 269 views
  • 0 likes
  • 2 in conversation