A series of recodes of one variable that creates 4 new variables.
Locked
 
|
I wish to create the multilevel Clinical Classification Software (CCS) created by the Healthcare Cost and Utilization Project (H-CUP) (https://www.hcup-us.ahrq.gov/toolssoftware/ccs/ccs.jsp). What they provide is a CSV file with ICD-9 codes in the first column with 4 more columns to be created by the user depending upon how specific you wish to get with the particular diagnosis. For example, with the codes below, the diagnosis goes from "Infectious and parasitic diseases" to "Bacterial infection" to "Septicemia (except in labor)" and then finally, "Streptococcal septicemia".
Altogether my data set has about 15,000 ICD-9 codes and I wish to create 4 new variables that are basically recodes of that ICD-9 code. I am almost resigned to create 4 sets of syntax, each one recoding for a particular level. ICD9-CM CODE CCS LVL 1 CCS LVL 2 CCS LVL 3 CCS LVL 4 0380 1 1.1 1.1.2 1.1.2.1 0381 1 1.1 1.1.2 1.1.2.2 03810 1 1.1 1.1.2 1.1.2.2 03811 1 1.1 1.1.2 1.1.2.2 03812 1 1.1 1.1.2 1.1.2.2 03819 1 1.1 1.1.2 1.1.2.2 03842 1 1.1 1.1.2 1.1.2.3 Is there an easier way to do this? I had thought there might be a way to loop over each ICD-9 code and recode to create each of the four levels based on the one ICD-9 code. |
Re: A series of recodes of one variable that creates 4 new variables.
Administrator
|
You might have better luck if you were to specify the rules for construction of LVL 1 - LVL 4?
What governs the transition to x.1.1 - x.1.2 - x.1.3? This question leaves much to the imagination and I for one am not inspired to throw air code at a underspecified query. ---
Please reply to the list and not to my personal email.
Those desiring my consulting or training services please feel free to email me. --- "Nolite dare sanctum canibus neque mittatis margaritas vestras ante porcos ne forte conculcent eas pedibus suis." Cum es damnatorum possederunt porcos iens ut salire off sanguinum cliff in abyssum?" |
Re: A series of recodes of one variable that creates 4 new variables.
|
|
In reply to this post by jhwagner
I agree with David, that this request if fairly unintelligible.
=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD
However. In my own small use of the ICD codes, the main, practical recoding that was called for was the truncation of digits at the end. Thus, I may have had a code of "038323" -- which in the ICD might be listed as "383.23". Selective truncating of significant digits could give me "38320" and "38300". That seems relevant to the question. From the few examples shown for "4 additional codes", I see that it is entirely consistent to generate the 3rd, 2nd, and 1st from the 4th one, by removing two characters successively from the end. Is that the simple "generating" function that David asked for? Or do you need to tell us something else? -- Rich Ulrich > Date: Sun, 28 Jun 2015 18:08:47 -0700 > From: [hidden email] > Subject: A series of recodes of one variable that creates 4 new variables. > To: [hidden email] > > I wish to create the multilevel Clinical Classification Software (CCS) > created by the Healthcare Cost and Utilization Project (H-CUP) > (https://www.hcup-us.ahrq.gov/toolssoftware/ccs/ccs.jsp). What they provide > is a CSV file with ICD-9 codes in the first column with 4 more columns to be > created by the user depending upon how specific you wish to get with the > particular diagnosis. For example, with the codes below, the diagnosis goes > from "Infectious and parasitic diseases" to "Bacterial infection" to > "Septicemia (except in labor)" and then finally, "Streptococcal septicemia". > > Altogether my data set has about 15,000 ICD-9 codes and I wish to create 4 > new variables that are basically recodes of that ICD-9 code. I am almost > resigned to create 4 sets of syntax, each one recoding for a particular > level. > > ICD9-CM CODE CCS LVL 1 CCS LVL 2 CCS LVL 3 CCS LVL > 4 > 0380 1 1.1 1.1.2 > 1.1.2.1 > 0381 1 1.1 1.1.2 > 1.1.2.2 > 03810 1 1.1 1.1.2 > 1.1.2.2 > 03811 1 1.1 1.1.2 > 1.1.2.2 > 03812 1 1.1 1.1.2 > 1.1.2.2 > 03819 1 1.1 1.1.2 > 1.1.2.2 > 03842 1 1.1 1.1.2 > 1.1.2.3 > > Is there an easier way to do this? I had thought there might be a way to > loop over each ICD-9 code and recode to create each of the four levels based > on the one ICD-9 code. > |
|
|
In reply to this post by jhwagner
----- Original Message -----
From: "jhwagner" <[hidden email]>
To: <[hidden email]>
Sent: Sunday, June 28, 2015 9:08 PM
Subject: A series of recodes of one variable that
creates 4 new variables. >I wish to create the multilevel Clinical
Classification Software (CCS)
> created by the Healthcare Cost and Utilization Project (H-CUP) > (https://www.hcup-us.ahrq.gov/toolssoftware/ccs/ccs.jsp). What they provide > is a CSV file with ICD-9 codes in the first column with 4 more columns to be > created by the user depending upon how specific you wish to get with the > particular diagnosis. For example, with the codes below, the diagnosis goes > from "Infectious and parasitic diseases" to "Bacterial infection" to > "Septicemia (except in labor)" and then finally, "Streptococcal septicemia". > > Altogether my data set has about 15,000 ICD-9 codes and I wish to create 4 > new variables that are basically recodes of that ICD-9 code. I am almost > resigned to create 4 sets of syntax, each one recoding for a particular > level. > > ICD9-CM_CODE CCS_LVL_1 CCS_LVL_2 CCS_LVL_3 CCS_LVL_4 > 0380 1 1.1 1.1.2 1.1.2.1 > 0381 1 1.1 1.1.2 1.1.2.2 > 03810 1 1.1 1.1.2 1.1.2.2 [rest of post snipped]
NOTE: I converted the font to Courier because I
wanted
a monospaced representation of the data. I also
reformatted
the small data table at the end of the post above
in
order to see more clearly what is going
on.
It has taken some time for me to figure out (1)
what
the OP was talking about, (2) where the data was
located
and their format was, and (3) what SPSS syntax
would be
needed.
The good new is, I think, the data file that the
OP wants
already exists: it is ccs_multi_dx_tool_2015.csv
-- this
file comes from website identified above by the OP
and here
are the lines that correspond to the table
above:
In Excel 2010:
Column A: Contains ICD-9 Diagnosis Code (e.g.,
'0380')
Column B: Top level of Category Hierarchy ("1" for
these Dx)
Column C: Top level of Name (Infectious and
parasitic diseases)
Column D: 2nd level down category code
("1.1")
Column E: 2nd level down category name (Bacterial
infection)
Column F: 3rd level down category code
("1.1.2")
Column G: 3rd level down category name (Septicemia
etc)
Column H: 4th Level down category code
("1.1.2.1")
Column I: 4th level down category name
(Streptococcal septicemia)
The OP can decide what modifications need to be
done
(e.g., remove single quotes around numbers) and so
on.
After making the changes, import into SPSS and
rock and
roll. Unless I am completely wrong about
everything.
-Mike Palij
New York University
P.S. The bill is in the mail.
|
||||||||||||||||||||||||||||||
Re: A series of recodes of one variable that creates 4 new variables.
|
|
Yup. Mike Palij  is correct (beat me to it). The CCS site has the complete crosswalk from ICD9 to CCS coding. Of course, when you think about it, it would NO sense if the crosswalk did NOT exist in an easily accessible form. I figured it out yesterday but was too slow in responding. Crosswalk files  found in the zip file are ccs_multi_dx_tool_2015.csv    15073 lines counting variable name header ccs_multi_pr_tool_2015.csv     3949 lines counting variable name header ... Mark Miller On Mon, Jun 29, 2015 at 8:48 AM, Mike Palij <[hidden email]> wrote:
|
|||||||||||||||||||||||||||||||
|
|

I rarely work with ICD codes and didn't know
what relationship the CCS
coding had to it. After looking at the
materials on the website and in the
Zip file Mark links to below, it became
obvious to me that there had to
be some established linkage between the two
systems. Finally stumbled
across the Dx csv file and realized that was
the solution. However,
I agree with David Marso and Rich Ulrich that
the OP was unclear
in his original post and assumed that people
would be familiar with
both the ICD and CCS systems.
-Mike Palij
New York University
----- Original Message -----
===================== To manage your subscription to SPSSX-L, send a message to [hidden email] (not to SPSSX-L), with no body text except the command. To leave the list, send the command SIGNOFF SPSSX-L For a list of commands to manage subscriptions, send the command INFO REFCARD |
|||||||||||||||||||||||||||||||
|
|
This post was updated on .
In reply to this post by David Marso
What governs the transition to x.1.1 - x.1.2 - x.1.3? The ICD-9 Codes. Each of the 15,000 plus different ICD-9 codes can be categorized into x, x.x., x.x.x, and in some cases, x.x.x.x with each level becoming more granular. These are all based on the .csv file located in the link I provided. You can look at the .csv file if you wish but it isn't important. I will however use it to crosswalk my ICD-9 codes into the four variables.
I have a dataset of millions of records with ICD-9 codes (among many other variables). I need to categorize each of these ICD-9 codes into the 4 different categories: x, x.x, x.x.x, x.x.x.x So based on the first line in my example, I want to compute CCSLVL1 = 1, CCSLVL2 = 1.1, CCSLVL3 = 1.1.2, and CCSLVL4 = 1.1.2.1 every time the ICD-9 code = 0380. |
|
|
A few points:
(1) The file "ccs_multi_dx_tool_2015.csv" contains the ICD-9 code and the CSS hierarchy level where it goes into, that is: Top level = CSSLVL1 NOTE: All ICD-9 diagnoses have a value for this variable in the range 1-18 representing the categories described here: https://en.wikipedia.org/wiki/List_of_ICD-9_codes Also, when CSSLVL1 = 18, the other CSSLVL vars have no values 2nd from Top = CSSLVL2 3rd from Top = CSSLVL3 4th from Top = CSSLVL4 I suggest you clean it up (e.g., remove single quotes from the above variables and so on) and read it into SPSS. (2) In SPSS, once you have the Dx file cleaned up and changed the formats of the variables into ones that you want (some string variables might be reduced in length), save the file as an SPSS data file. (3) I don't know the structure of your other file, the one with millions and millions of records but, presumably, each "case" (however defined) has an ICD-9 code in it. Do a MATCH FILES using the saved SPSS data file with the CSS variables as a TABLE. This will link each case in your "BIG FILE" with all of the CSSLVL variables in the other files. You now have all 4 categories added to your "BIG FILE" without additional recoding or whatever. I am sure if my logic above is off, one of the more knowledgeable people on the list will point it out and correct me. -Mike Palij New York University [hidden email] ----- Original Message ----- From: "jhwagner" <[hidden email]> To: <[hidden email]> Sent: Monday, June 29, 2015 7:14 PM Subject: Re: A series of recodes of one variable that creates 4 new variables. > What governs the transition to x.1.1 - x.1.2 - x.1.3? The ICD-9 > Codes. Each > of the 15,000 plus different ICD-9 codes can be categorized into x, > x.x., > x.x.x, and in some cases, x.x.x.x with each level becoming more > granular. > These are all based on the .csv file located in the link I provided. > You can > look at the .csv file if you wish but it isn't important. I will > however use > it to crosswalk my ICD-9 codes into the four variables. > > I have a dataset of millions of records with ICD-9 codes (among many > other > variables). I need to categorize each of these ICD-9 codes into the 4 > different categories: x, x.x, x.x.x, x.x.x.x > > So based on the first line in my example, I want to compute CSSLVL1 = > 1, > CSSLVL2 = 1.1, CSSLVL3 = 1.1.2, and CSSLVL4 = 1.1.2.1 every time the > ICD-9 > code = 0380. ===================== To manage your subscription to SPSSX-L, send a message to [hidden email] (not to SPSSX-L), with no body text except the command. To leave the list, send the command SIGNOFF SPSSX-L For a list of commands to manage subscriptions, send the command INFO REFCARD |
Re: A series of recodes of one variable that creates 4 new variables.
|
|
In reply to this post by jhwagner
"Compute" would be a whole lot more time-consuming than doing
=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD
a one-time match. You take the (sorted) file that has 0380 1 1.1 1.1.2 1.1.2.1 for its lines, and match it, saying FILE= "ICD9file.sav"/ TABLE= "CSSCODES.sav" to the ICD9-sorted file. -- Rich Ulrich > Date: Mon, 29 Jun 2015 16:14:50 -0700 > From: [hidden email] > Subject: Re: A series of recodes of one variable that creates 4 new variables. > To: [hidden email] > > What governs the transition to x.1.1 - x.1.2 - x.1.3? The ICD-9 Codes. Each > of the 15,000 plus different ICD-9 codes can be categorized into x, x.x., > x.x.x, and in some cases, x.x.x.x with each level becoming more granular. > These are all based on the .csv file located in the link I provided. You can > look at the .csv file if you wish but it isn't important. I will however use > it to crosswalk my ICD-9 codes into the four variables. > > I have a dataset of millions of records with ICD-9 codes (among many other > variables). I need to categorize each of these ICD-9 codes into the 4 > different categories: x, x.x, x.x.x, x.x.x.x > > So based on the first line in my example, I want to compute CSSLVL1 = 1, > CSSLVL2 = 1.1, CSSLVL3 = 1.1.2, and CSSLVL4 = 1.1.2.1 every time the ICD-9 > code = 0380. > > ... |
|
|
I see that Rich had the same solution in mind
that I did.
One additional thought that occurred to me
after I sent
my post is that the OP might want to create a
variable
that would indicate how many levels have
values.
As I mention in my post, when CSSLVL1 =
18,
CSSLVL2, CSSLVL3, and CSSLVL4 are
missing.
I guess the question comes down to whether
every time
the OP uses the ICD-9 code he wants to use all
4
level variables, regardless if they have a
value or not,
or he wants to use only the lowest level
variable with
a value (e.g., CSSLVL4). The zip file
with the Excel file
has a PDF with the name
"CCSCategoryNames_FullLabels.pdf"
and gives the lowest level value for Dx
(starting on
page 10 -- note that the pages are not
numbered)
gives one an idea of which of the four levels
is
appropriate to use for a
diagnosis.
-Mike Palij
New York University
===================== To manage your subscription to SPSSX-L, send a message to [hidden email] (not to SPSSX-L), with no body text except the command. To leave the list, send the command SIGNOFF SPSSX-L For a list of commands to manage subscriptions, send the command INFO REFCARD |
«
Return to SPSSX Discussion
|
1 view|%1 views
| Free forum by Nabble | Edit this page |