Actuarial Outpost
 
Go Back   Actuarial Outpost > Actuarial Discussion Forum > Software & Technology
FlashChat Actuarial Discussion Preliminary Exams CAS/SOA Exams Cyberchat Around the World Suggestions


Upload your resume securely at https://www.dwsimpson.com
to be contacted when our jobs meet your skills and objectives.


Reply
 
Thread Tools Search this Thread Display Modes
  #161  
Old 11-07-2013, 04:16 PM
Gareth Keenan's Avatar
Gareth Keenan Gareth Keenan is offline
Member
CAS
 
Join Date: Feb 2006
Location: Garden State
Studying for #9
Favorite beer: butter
Posts: 1,308
Default When does my claim breach the threshold

The request from up on high is simple. In each of the past few years, how many claims breached a large loss threshold. The threshold is not the same for all claims. I have identified all of the appropriate thresholds and which claims breach their thresholds based on cumulative paid + reserve amounts through the most recent quarter.

However, what I'm hoping to do in SAS is identify the calendar quarter in which cumulative paid + cumulative reserves >= threshold.

My data includes incremental data by quarter. For example, quarter 1 would have a 0 for paid, and a 3000 for reserves when a claims is initially filed, but quarter 2 might have a 500 in Paid and 500 in reserves if $500 was paid, and the total incurred amount was raised to $4000.

Also adding to the complexity, is that the claims don't all originate in the same calendar/accident quarter.

If my data consisted of only Claim(for claim number), Paid(for paid), Reserve(for reserves), threshold(the flag for when it becomes relevant), and Cal_Qtr(for calendar quarter), and I have all history from when the claim first appeared in our data to present, can it be done in the one SAS DATA/PROC step to find out when each claim breached the relevant threshold?

I'd prefer to avoid having to perform several proc sql's where I truncate the quarter through which incremental transactions can be included in the data.

Does that make sense? Any suggestions?

Cheers,

Gareth Keenan
Reply With Quote
  #162  
Old 11-07-2013, 04:52 PM
BassFreq's Avatar
BassFreq BassFreq is offline
Member
CAS
 
Join Date: Jun 2003
Location: Chicago
Studying for all eternity
Favorite beer: Duff
Posts: 1,708
Blog Entries: 2
Default

Not the prettiest or most efficient way to do it, but I think this will get you there. The data step at the beginning was just to create some sample data for me to play with.
Code:

data mydata;
   do claim=1to100;
       threshold=1+claim/10;
       do cal_qtr=1to10;
           paid=ranuni(1234);
           output;
       end;
   end;
run;
 
procsql;
createtable bassfreq as
  select claim,min(cal_qtri) as cal_qtr_of_breach
  from (
     select i.claim,i.cal_qtri
     from
       (selectdistinct claim,cal_qtr as cal_qtri,threshold from mydata) i
     leftjoin mydata t
     on i.claim=t.claim and i.cal_qtri ge t.cal_qtr
     groupby i.claim,i.cal_qtri
     having i.threshold le sum(t.paid)
       )
  groupby claim;
quit;
__________________
If at first you don't succeed, you have one data point.
Res ipsa loquitur, sed quid in infernos dicet?
Reply With Quote
  #163  
Old 11-07-2013, 05:40 PM
Gareth Keenan's Avatar
Gareth Keenan Gareth Keenan is offline
Member
CAS
 
Join Date: Feb 2006
Location: Garden State
Studying for #9
Favorite beer: butter
Posts: 1,308
Default

Yup, i think that's the ticket. I had to make some minor adjustments for the actual variable names, and I need to validate by manually looking at one or two claims, but that appears to be the ticket.

I'm just not at the SAS level where I'm comfortable embedding a select from in a select from in a proc sql. That's definitely intermediate to advanced level SAS.

Thanks Bassfreq!

Gareth Keenan

PS: One of the myriad reason Space balls is hilarious is the merchandising scene, but what makes the scene particularly hilarious, was that George Lucas gave the green light for a star wars parody as long as there was no actual merchandising from the movie. So those guys knew there would be no flame thrower!

EDIT:visual inspection confirms the result!

Last edited by Gareth Keenan; 11-07-2013 at 05:56 PM.. Reason: confirmation
Reply With Quote
  #164  
Old 11-07-2013, 06:08 PM
BassFreq's Avatar
BassFreq BassFreq is offline
Member
CAS
 
Join Date: Jun 2003
Location: Chicago
Studying for all eternity
Favorite beer: Duff
Posts: 1,708
Blog Entries: 2
Default

As long as there is no merchandising around the query and as long as you keep the table name bassfreq, you're welcome.

Avoiding nested queries is a good thing. They tend to be less efficient than other approches, but sometimes its easier to write a query that way...especially when trying to squeeze multiple steps into a single step.
__________________
If at first you don't succeed, you have one data point.
Res ipsa loquitur, sed quid in infernos dicet?
Reply With Quote
  #165  
Old 11-08-2013, 11:06 AM
SpaceActuary's Avatar
SpaceActuary SpaceActuary is offline
Member
 
Join Date: Apr 2010
College: Drake U
Posts: 601
Default

Gareth, I know BassFreq already found a solution to your problem, but here's an alternative solution that IMO is a lot clearer:

Code:
data mydata;
   do claim=1 to 100;
       threshold=1+claim/10;
       do cal_qtr=1 to 10;
           paid=ranuni(1234);
           output;
       end;
   end;
run;

* in case your data isn't already sorted;
proc sort data=mydata;
  by claim cal_qtr;
run;

data spaceactuary(keep=claim threshold cal_qtr_of_breach paid_to_date);
    set mydata;
    by claim cal_qtr;

    * don't overwrite these variables on each subsequent record;
    retain paid_to_date cal_qtr_of_breach; 

    * unless it's the first record for the claim;
    if first.claim then do;
        cal_qtr_of_breach = . ;
        paid_to_date = 0 ;
    end;

    * calculate a running total;
    paid_to_date = paid_to_date + paid;

    * determine if the claim has breached the threshold, if yes then output;
    if paid_to_date > threshold and cal_qtr_of_breach = . then do;
        cal_qtr_of_breach = cal_qtr;
        output;
    end;
run;
__________________
And all this science, I don't understand; It's just my job five days a week...a rocket man, a rocket man...

"I'm from Iowa. I only work in space." - Adm. James T. Kirk
Reply With Quote
  #166  
Old 11-08-2013, 11:18 AM
BassFreq's Avatar
BassFreq BassFreq is offline
Member
CAS
 
Join Date: Jun 2003
Location: Chicago
Studying for all eternity
Favorite beer: Duff
Posts: 1,708
Blog Entries: 2
Default

Nice! I approve. SpaceActuary's approach is much more efficient than mine and produces the same results. If you remove "threshold" and "paid_to_date" from his keep option, then the two produce the exact same results.
__________________
If at first you don't succeed, you have one data point.
Res ipsa loquitur, sed quid in infernos dicet?
Reply With Quote
  #167  
Old 04-13-2014, 02:41 AM
Elsaball Elsaball is offline
Member
CAS
 
Join Date: Jun 2010
Studying for nothing
Posts: 1,753
Default

I am pretty new to SAS, so if this is simple, be easy on me. How can I refer to a data step value (in an array column) as an argument to a macro? For example, lets say I have an array of 10 temperature columns in Fahrenheit and would like the macro to convert each to Celsius. How could I do this?
Reply With Quote
  #168  
Old 04-13-2014, 12:02 PM
BassFreq's Avatar
BassFreq BassFreq is offline
Member
CAS
 
Join Date: Jun 2003
Location: Chicago
Studying for all eternity
Favorite beer: Duff
Posts: 1,708
Blog Entries: 2
Default

Let's say you've already defined arrays Ftemps and Ctemps. Also, you have a macro to convert degrees F into degrees C called %convert_temp.

Let's say we've created %convert_temp as follows:
Code:
%macro contvert_temp(F);
    ( (&F-32)*5/9 )
%mend;
Then within your data step you can have something like
Code:
do i =1 to 10;
    Ctemps(i) = %convert_temp( Ftemps(i) );
end;
__________________
If at first you don't succeed, you have one data point.
Res ipsa loquitur, sed quid in infernos dicet?
Reply With Quote
  #169  
Old 04-13-2014, 01:46 PM
Elsaball Elsaball is offline
Member
CAS
 
Join Date: Jun 2010
Studying for nothing
Posts: 1,753
Default

Quote:
Originally Posted by BassFreq View Post
Let's say you've already defined arrays Ftemps and Ctemps. Also, you have a macro to convert degrees F into degrees C called %convert_temp.

Let's say we've created %convert_temp as follows:
Code:
%macro contvert_temp(F);
    ( (&F-32)*5/9 )
%mend;
Then within your data step you can have something like
Code:
do i =1 to 10;
    Ctemps(i) = %convert_temp( Ftemps(i) );
end;
You can just put a reference to a data element as an argument to a macro? When I try that the macro tries to run the text "Ftempts(i)" and not the value of the data element. And I've tried converting the array to a macro variable with no success by using CALL SYSMPUT with no success.
Reply With Quote
  #170  
Old 04-16-2014, 11:09 AM
BassFreq's Avatar
BassFreq BassFreq is offline
Member
CAS
 
Join Date: Jun 2003
Location: Chicago
Studying for all eternity
Favorite beer: Duff
Posts: 1,708
Blog Entries: 2
Default

Quote:
Originally Posted by Elsaball View Post
You can just put a reference to a data element as an argument to a macro? When I try that the macro tries to run the text "Ftempts(i)" and not the value of the data element. And I've tried converting the array to a macro variable with no success by using CALL SYSMPUT with no success.
Yes, you can. Run the following:

%macro convert_temp(F);
round( (&F-
32)*5/9, 0.1 )
%mend;
 
data_null_;
F =
72;
c = %convert_temp( F );
put F " Farenheit = " C " celcius, and that is how you pass a variable to a macro.";
run;
__________________
If at first you don't succeed, you have one data point.
Res ipsa loquitur, sed quid in infernos dicet?
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off


All times are GMT -4. The time now is 03:24 AM.


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
*PLEASE NOTE: Posts are not checked for accuracy, and do not
represent the views of the Actuarial Outpost or its sponsors.
Page generated in 0.34608 seconds with 10 queries