BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Jacob
Fluorite | Level 6

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

2 REPLIES 2
Reeza
Super User

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
Fluorite | Level 6

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. 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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