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.
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.
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.
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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.