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

Browse Open Actuarial Jobs

Life  Health  Casualty  Pension  Entry Level  All Jobs  Salaries


Reply
 
Thread Tools Search this Thread Display Modes
  #11  
Old 12-06-2019, 01:14 PM
nonlnear nonlnear is offline
Member
Non-Actuary
 
Join Date: May 2010
Posts: 32,087
Default

Why on earth would anyone begin learning MS Access in 2019?
Reply With Quote
  #12  
Old 12-06-2019, 02:33 PM
1695814's Avatar
1695814 1695814 is offline
Member
SOA AAA
 
Join Date: Aug 2002
Studying for nothing. I quit.
Favorite beer: Root
Posts: 39,034
Default

Quote:
Originally Posted by nonlnear View Post
Why on earth would anyone begin learning MS Access in 2019?
Short story:

Long story:
Spoiler:
Reply With Quote
  #13  
Old 12-06-2019, 02:35 PM
Maphisto's Sidekick's Avatar
Maphisto's Sidekick Maphisto's Sidekick is offline
Member
CAS
 
Join Date: Nov 2001
Location: South Park Genetics Lab
College: Ardnox
Favorite beer: The kind with alcohol
Posts: 3,398
Default

Quote:
Originally Posted by nonlnear View Post
Why on earth would anyone begin learning MS Access in 2019?
Because Corporate IT creates headaches when trying to set up access and space for SQL server, management likes reporting in Excel/PowerPoint, and you want auto-updated query output embedded in the Excel/PowerPoint file you pass on each month?

And while it's not something a beginner would do, I have on more than one occasion prototyped a pricing algorithm or some kind of system in Excel or Access as a component of specs for something I want IT to build. Excel/Access are lowest-common-denominator tools that can be more effective than text descriptions when it comes to communicating "here's how it should work".
Reply With Quote
  #14  
Old 12-06-2019, 04:21 PM
IANAE IANAE is offline
Member
CAS AAA
 
Join Date: Oct 2017
Posts: 280
Default

Quote:
Originally Posted by nonlnear View Post
Why on earth would anyone begin learning MS Access in 2019?
For the reasons cited by Maphisto's Sidekick but typically speed of execution with low/nil red tape to get started & done.

If a model needs to scale in production then cba will show the way.
Reply With Quote
  #15  
Old 12-07-2019, 08:43 PM
1695814's Avatar
1695814 1695814 is offline
Member
SOA AAA
 
Join Date: Aug 2002
Studying for nothing. I quit.
Favorite beer: Root
Posts: 39,034
Default

Help me program this in Access:

In October, we had a bunch of policies that also had ADB riders.
In November, we lost a bunch of policies...many of them had ADB riders...maybe, perhaps, we just lost the ADB rider.

I want to find all of the base policies that lost an ADB rider.
Then, I also want to know if that base policy is still around or not.

If a rider coverage terminated, it will not be on the November file.
...likewise for the base coverage. (Also, if a base coverage terminated then the rider did, too.)


Here's some sample data:

October
Policy, Plancode
A1, Base
A1, ADB
A1, Waiver
B2, Base
B2, ADB
B2, Waiver
C3, Base
C3, ADB

November
Policy, Plancode
B2, Base
B2, Waiver
C3, Base
C3, ADB


I should get two sets of output (or something similar):

Base policies terminated
A1

Base policies still around, just ADB terminated
B2


I can program this in SAS lickety split, but Access/SQL confounds me.

There are most certainly better ways to do this, but this is how I'd do it in SAS (my SAS skills are a bit rusty...the syntax might not be completely up to snuff (kids, just say "no" to tobacco)):
Code:
DATA OCTADB OCTBASE;
INFILE OCT;
INPUT
 @1 POLICY $CHAR15.
 @16 PLANCODE $CHAR15.
 ;
KEEP POLICY;
IF PLANCODE = 'ADB' THEN OUTPUT OCTADB;
ELSE IF PLANCODE = 'BASE' THEN OUTPUT OCTBASE;
RUN;

PROC SORT DATA=OCTBASE;
 BY POLICY;
RUN;

PROC SORT DATA=OCTADB;
 BY POLICY;
RUN;

/* FIND ALL OF THE BASE POLICIES THAT HAD AN ADB RIDER */
DATA OCTBASE;
 MERGE OCTBASE OCTADB(IN=B);
  BY POLICY;
 IF B;
RUN;

DATA NOVADB NOVBASE;
INFILE NOV;
INPUT
 @1 POLICY $CHAR15.
 @16 PLANCODE $CHAR15.
 ;
KEEP POLICY;
IF PLANCODE = 'ADB' THEN OUTPUT NOVADB;
ELSE IF PLANCODE = 'BASE' THEN OUTPUT NOVBASE;
RUN;

PROC SORT DATA=NOVBASE;
 BY POLICY;
RUN;

PROC SORT DATA=NOVADB;
 BY POLICY;
RUN;

/* FIND ALL OF THE BASE POLICIES THAT HAD AN ADB RIDER */
DATA NOVBASE;
 MERGE NOVBASE NOVADB(IN=B);
  BY POLICY;
 IF B;
RUN;

/* FIND THE BASE POLICIES THAT TERMINATED */
DATA TERMBASE;
 MERGE OCTBASE(IN=A) NOVBASE(IN=B);
  BY POLICY;
 IF A AND NOT B;
RUN;

PROC PRINT; /* OR SOMETHING */
RUN;

/* FIND THE ADB RIDERS THAT TERMINATED WHEN THE BASE POLICY DID NOT */
DATA TERMADB;
 MERGE OCTADB(IN=A) NOVADB(IN=B);
  BY POLICY;
 IF A AND NOT B;
RUN;

DATA INFBASE;
 MERGE TERMADB(IN=A) OCTBASE(IN=B) NOVBASE(IN=C);
  BY POLICY;
 IF A AND B AND C;
RUN;

PROC PRINT; /* OR SOMETHING */
RUN;
...or something like that. I can't test that code since I don't have SAS anymore. Regardless, I don't know how to set this sort of thing up in Access.

Last edited by 1695814; 12-09-2019 at 10:13 AM..
Reply With Quote
  #16  
Old 12-08-2019, 12:03 AM
Steve Grondin Steve Grondin is offline
Member
SOA AAA
 
Join Date: Nov 2001
Posts: 7,320
Default

All this is pseudo-code, no guarantees it works, so you can figure it out.

You want a list of contracts that had ADB in Oct, but no longer have ADB but are in-force in Nov.

First one is easy.

SELECT DISTINCT Policy FROM OctList WHERE PLancode = ADB

I use the DISTINCT to handle the case where one policy has more than one ADB riders.

Second is a little more complicated:

Get a list of all contracts with ADB, and exclude that list from the list of all November policies. That's a join where the policy in ADB list is null.

SELECT B.POLICY FROM
(SELECT DISTINCT Policy FROM NovList WHERE PLancode = ADB) AS A
RIGHT JOIN
(SELECT DISTINCT Policy FROM NovList) AS B
ON A.POLICY = B.POLICY
WHERE A.POLICY IS NULL

Then join these two (inner join) and you are good to go.
Reply With Quote
  #17  
Old 12-09-2019, 12:05 PM
1695814's Avatar
1695814 1695814 is offline
Member
SOA AAA
 
Join Date: Aug 2002
Studying for nothing. I quit.
Favorite beer: Root
Posts: 39,034
Default

Quote:
Originally Posted by Steve Grondin View Post
All this is pseudo-code, no guarantees it works, so you can figure it out.

Spoiler:
You want a list of contracts that had ADB in Oct, but no longer have ADB but are in-force in Nov.

First one is easy.

SELECT DISTINCT Policy FROM OctList WHERE PLancode = ADB

I use the DISTINCT to handle the case where one policy has more than one ADB riders.

Second is a little more complicated:

Get a list of all contracts with ADB, and exclude that list from the list of all November policies. That's a join where the policy in ADB list is null.

SELECT B.POLICY FROM
(SELECT DISTINCT Policy FROM NovList WHERE PLancode = ADB) AS A
RIGHT JOIN
(SELECT DISTINCT Policy FROM NovList) AS B
ON A.POLICY = B.POLICY
WHERE A.POLICY IS NULL

Then join these two (inner join) and you are good to go.
That mostly works, so thank you for that.

I feel like SQL/Access should be much easier to learn but it's just not intuitive to me.

Last edited by 1695814; 12-10-2019 at 12:01 AM..
Reply With Quote
  #18  
Old 12-16-2019, 05:17 PM
1695814's Avatar
1695814 1695814 is offline
Member
SOA AAA
 
Join Date: Aug 2002
Studying for nothing. I quit.
Favorite beer: Root
Posts: 39,034
Default



I have a table called Valuation. It has the fields Policy, Coverage, & Company.
I have another table called Reserves. It has two fields, UniqueID and StatRes.
UniqueID is a concatenation of Policy, Coverage, & Company

So, here's one sample of data:

Valuation
Policy = ABC001
Coverage = 1
Company = XYZ

Reserves
UniqueID = ABC0011XYZ
StatRes= 42.00


I want to join those two fields, but I don't know how to set it up.
In SAS I would add a field to Valuation called UniqueID = Policy !! Coverage !! Company;
Then I could merge the two by UniqueID.
I don't know how to set that up in Access or SQL.

Here's my SAS code
Code:
DATA VALUATION;
INFILE VALUATION;
INPUT 
@01 POLICY $CHAR10.
@11 COVERAGE $2.
@13 COMPANY $CHAR3.
;
UNIQUEID = POLICY!!TRIM(COVERAGE)!!COMPANY; /* or something like that */
RUN;
 
PROC SORT;
BY UNIQUEID;
RUN;

DATA RESERVES;
INFILE RESERVES;
INPUT
@01 UNIQUEID $CHAR15.
@16 STATRES 14.2
;
RUN;

PROC SORT;
BY UNIQUEID;
RUN;

DATA MERGE;
MERGE VALUATION(IN=A) RESERVES(IN=B)
BY UNIQUEID;
IF A;
IF NOT B THEN STATRES = 0;
RUN;

PROC MEANS SUM;
VAR STATRES;
TITLE 'HERE ARE YOUR STINKING RESERVES';
RUN;

Last edited by 1695814; 12-16-2019 at 06:05 PM..
Reply With Quote
  #19  
Old 12-16-2019, 05:22 PM
1695814's Avatar
1695814 1695814 is offline
Member
SOA AAA
 
Join Date: Aug 2002
Studying for nothing. I quit.
Favorite beer: Root
Posts: 39,034
Default

Based on other stuff I've seen online...I'm trying something like this but I get a Type mismatch error I think because Coverage is numeric.

Code:
SELECT  Valuation.Policy + Valuation.Coverage + Valuation.Company AS CurrValID
FROM Reserves INNER JOIN
Valuation ON Reserves.UniqueID = Valuation.Policy + Valuation.Coverage + Valuation.Company
;

Last edited by 1695814; 12-16-2019 at 05:25 PM..
Reply With Quote
  #20  
Old 12-16-2019, 10:37 PM
exponentc exponentc is offline
Member
CAS
 
Join Date: Oct 2018
College: JHU
Posts: 174
Default

If your request can be understood by computers and translated into sql code, I guess that would make things a lot easier.
Reply With Quote
Reply

Tags
access

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 12:18 AM.


Powered by vBulletin®
Copyright ©2000 - 2020, 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.18378 seconds with 10 queries