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


Reply
 
Thread Tools Search this Thread Display Modes
  #31  
Old 05-01-2019, 09:24 AM
rgreenlee's Avatar
rgreenlee rgreenlee is offline
Member
Non-Actuary
 
Join Date: Jun 2014
Location: in a van down by the river
Posts: 1,075
Default

I like the data in the format you already have because it is almost grouper model ready. I'm not sure what you will be doing with it side by side
Reply With Quote
  #32  
Old 05-01-2019, 09:26 AM
T-roy's Avatar
T-roy T-roy is offline
Member
SOA
 
Join Date: May 2002
College: UW Whitewater
Favorite beer: FFF GBH/NG Moon Man
Posts: 8,867
Blog Entries: 1
Default

Quote:
Originally Posted by rgreenlee View Post
Note that if you have lots of diagnosis codes, you can do this process with as many left outer joins

I thought about this. I'd have to somehow make three tables. but then I would have to figure out how to number each claim_sk and reset the next claim_sk. But then I'd have to export a table and re-import it.
__________________
Quote:
Originally Posted by ORLYLOL View Post
Beer is ****ing amazing
Reply With Quote
  #33  
Old 05-01-2019, 09:46 AM
Mathdube2 Mathdube2 is offline
Member
SOA
 
Join Date: Oct 2018
Posts: 40
Default

Do you always need to have like 59 in mod_code1 and GP in mod_code2 or you can have GP then 59?
Reply With Quote
  #34  
Old 05-01-2019, 09:55 AM
rgreenlee's Avatar
rgreenlee rgreenlee is offline
Member
Non-Actuary
 
Join Date: Jun 2014
Location: in a van down by the river
Posts: 1,075
Default

How many unique mod codes are there?

Something like max(case when mod_code='59' then '59' else '' end) as mod_code1 could suffice if there are only a few unique mod_code s
Reply With Quote
  #35  
Old 05-01-2019, 10:33 AM
T-roy's Avatar
T-roy T-roy is offline
Member
SOA
 
Join Date: May 2002
College: UW Whitewater
Favorite beer: FFF GBH/NG Moon Man
Posts: 8,867
Blog Entries: 1
Default

There are 200ish different codes and no pattern of the permutations. Sometimes I will see a 27 on a Radiology claim, which makes no sense to me. I want to see none (global) a 26 (phys) or TC (Tech).

I have a meeting with one of our new Data Integrity directors next week. So I've got a lot to talk to her about and make the data more actuarial.

This seems to work as I now get no duplicates, however, some claims may have truncated off the "right" procedure modifier:

Code:
select * from 
(Select 
claim_sk
,proc_code, 
,min(mod_code) over (partition by claim_detail_sk,proc_code) mod_code1
,max(mod_code) over (partition by claim_detail_sk,proc_code) mod_code2
FROM Claim_table
)dt
group by claim_sk,proc_code,mod_code1,mod_code2
order by claiml_sk, proc_code
Here is some output:

Code:
claim_sk proc_code mod_code1 mod_code2
387601948 99214 25 25
387617705 97530 96 HN
387618192 K0001 KJ RR
387624535 66761 79 RT
387624565 99392 25 25
387625877 73564 LT LT
387626804 87804 59 QW
387847142 74220 27 TC
The Original claims from the above output:
Code:
claim_sk proc_code proc_code_system_code mod_code
387601948	99214	HCPCS	25
387617705	97530	HCPCS	96
387617705	97530	HCPCS	HN
387618192	K0001	HCPCS	KJ
387618192	K0001	HCPCS	KX
387618192	K0001	HCPCS	RR
387624535	66761	HCPCS	79
387624535	66761	HCPCS	RT
387624565	99392	HCPCS	25
387625877	73564	HCPCS	LT
387626804	87804	HCPCS	59
387626804	87804	HCPCS	QW
387847142	74220	HCPCS	27
387847142	74220	HCPCS	TC
__________________
Quote:
Originally Posted by ORLYLOL View Post
Beer is ****ing amazing

Last edited by T-roy; 05-01-2019 at 10:51 AM..
Reply With Quote
  #36  
Old 05-01-2019, 11:16 AM
Mathdube2 Mathdube2 is offline
Member
SOA
 
Join Date: Oct 2018
Posts: 40
Default

Didn't test it and not familliar with over clause, but maybe you could try this in three successive SQL

1. Min function only with the over clause and group by claim // proc to get Mod_Code1

2. (1) Left join original table by claim // proc

3. Lead (Mod_code1, 1) over (partition …) mod_code2
Lead (Mod_code1, 2) over (partition …) mod_code3

Last edited by Mathdube2; 05-01-2019 at 11:20 AM..
Reply With Quote
  #37  
Old 05-01-2019, 11:20 AM
T-roy's Avatar
T-roy T-roy is offline
Member
SOA
 
Join Date: May 2002
College: UW Whitewater
Favorite beer: FFF GBH/NG Moon Man
Posts: 8,867
Blog Entries: 1
Default

I pulled 15 days of claims and out of 175,000 claims that I've pulled, 533 has 3 mod codes, and out of these there were only 2 claims that a "TC" didn't show up as a min or max (mod1 or mod2). I think I can live with that. So one can assume that there will be occurrences where I have the incorrect info to reprice the claim, but at this point it is trivial.

Thanks for the suggestions everyone. I appreciate it (plus its fun)
__________________
Quote:
Originally Posted by ORLYLOL View Post
Beer is ****ing amazing
Reply With Quote
  #38  
Old 05-01-2019, 11:52 AM
Meshuga's Avatar
Meshuga Meshuga is offline
Member
Non-Actuary
 
Join Date: Dec 2001
Posts: 12,776
Default

SQL Pivot: Converting Rows to Columns
__________________
I know I don't talk in my sleep. Someone at work would have told me by now.
Reply With Quote
  #39  
Old 05-01-2019, 11:54 AM
T-roy's Avatar
T-roy T-roy is offline
Member
SOA
 
Join Date: May 2002
College: UW Whitewater
Favorite beer: FFF GBH/NG Moon Man
Posts: 8,867
Blog Entries: 1
Default

I looked at the sql pivot, but there is 200 Modifiers that I'd have to code. I didn't want/think I really had to do that. Is that a site that you'd recommend in the future? I did the google search and didn't come up with much that was usable.
__________________
Quote:
Originally Posted by ORLYLOL View Post
Beer is ****ing amazing

Last edited by T-roy; 05-01-2019 at 12:38 PM..
Reply With Quote
  #40  
Old 05-01-2019, 01:15 PM
Steve Grondin Steve Grondin is offline
Member
SOA AAA
 
Join Date: Nov 2001
Posts: 6,784
Default

Quote:
Originally Posted by T-roy View Post
I looked at the sql pivot, but there is 200 Modifiers that I'd have to code. I didn't want/think I really had to do that. Is that a site that you'd recommend in the future? I did the google search and didn't come up with much that was usable.
I am not sure if this works, but where the list is specified, perhaps you could substitute SELECT DISTINCT mod_code FROM source WHERE mod_code is not NULL for the list.

This potential would give you lots of columns where you just want 3.

Last edited by Steve Grondin; 05-01-2019 at 01:19 PM..
Reply With Quote
Reply

Tags
microsoft sql

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 11:08 AM.


Powered by vBulletin®
Copyright ©2000 - 2019, 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.19099 seconds with 9 queries