Issues with loading the CCS tool with HCUP on STATA software

[Mac Smith]

Hi I am trying to work with the NIS data (HCUP). I have loaded the data over to stata successfully. Now when selecting the correct ICD9 or 10 code for the diagnosis does any one use the CCS tool ? I downloaded the 2020 CCS tool from the HCUP website, but it has a load file for SAS but not for stata. It just says in the instructions to modify the corresponding code for STATA, which I don't know how to. Any chance any experienced STATA user who has worked with the HCUP data can answer this question for me ?
Thanks

[Mike Lacy]

Have you looked at the Stata -icd9- command? I'm relatively naive here, but it sounds like it might help while you're waiting for a more experienced person to consider your question.

[Mac Smith]

Thanks Mike, I did look at that. I could not figure out how to use it. The CCS file I think is more accurate anyways as it gets updated every year at least for ICD 10.
I guess the other way of asking my question would be is how to convert a SAS DO (or equivalent) to a STATA DO file.

The following is the SAS file provided and needs converting to the STATA equivalent.


options mprint symbolgen mlogic;
/************************************************** ****************/
/* Title: ICD-10-PCS CCS SINGLE-LEVEL LOAD SOFTWARE */
/* */
/* PROGRAM: ICD10_Single_CCS_LOAD_PROGRAM.SAS */
/* */
/* Description: This program creates single-level ICD-10-PCS */
/* CCS categories for data using ICD-10-PCS */
/* diagnosis or procedure codes. */
/* */
/* There are two general sections to this program: */
/* */
/* 1) The first section creates temporary SAS */
/* informats using the ICD-10-PCS tool files. */
/* The single-level categories are located in */
/* columns 2 and 4. */
/* These informats are used in step 2 to create */
/* the single-level CCS variables. */
/* 2) The second section loops through the diagnosis */
/* and/or procedure arrays in your SAS dataset */
/* and assigns the single-level CCS categories. */
/* */
/************************************************** ****************/
* Path & name for the ICD-10-CM/PCS CCS tool ;

FILENAME INRAW1 'Location of CSV file: ccs_pr_icd10pcs_2020_1.csv' LRECL=300;
LIBNAME IN1 'Location of input discharge data';
LIBNAME OUT1 'Directory to store output file';

TITLE1 'CREATE ICD-10-PCS CCS SINGLE-LEVEL TOOL CATEGORIES';
TITLE2 'USE WITH DISCHARGE ADMINISTRATIVE DATA THAT HAS ICD-10-CM/PCS CODES';

/************************************************** ****************/
/* Macro Variables that must be set to define the characteristics*/
/* of your SAS discharge data. Change these values to match the */
/* number of diagnoses and procedures in your dataset. Change */
/* CORE to match the name of your dataset. */
/************************************************** ****************/

* Maximum number of PRs on any record; %LET NUMPR=46;
* Input SAS file member name; %LET CORE=YOUR_SAS_FILE_HERE;

/******************* SECTION 1: CREATE INFORMATS ******************/
/* SAS Load the ICD-10-PCS CCS single-level tool & convert */
/* into temporary SAS informats that will be used to assign the */
/* single-level CCS variable in the next step. */
/************************************************** ****************/

/* Procedures CCS */
DATA PRCCS ;
INFILE INRAW1 DSD DLM=',' END = EOF FIRSTOBS=2;
INPUT
START : $CHAR7.
LABEL : 4.
ICD10PCS_label : $CHAR100.
CCS_Label : $CHAR100.
Multi_lvl1 : $CHAR2.
Multi_lvl1_label : $CHAR100.
Multi_lvl2 : $CHAR5.
Multi_lvl2_label : $CHAR100.
;
RETAIN HLO " ";
FMTNAME = "I10PRCCS" ;
TYPE = "I" ;
OUTPUT;

IF EOF THEN DO ;
START = " " ;
LABEL = 0 ;
HLO = "O";
OUTPUT ;
END ;
RUN;

PROC FORMAT LIB=WORK CNTLIN = PRCCS ;
RUN;

/*********** SECTION 2: CREATE ICD-10-PCS SINGLE-LEVEL CCS CATS *******/
/* Create single-level CCS categories for PCS using the SAS */
/* informats created above & the SAS file you wish to augment. */
/* Users can change the names of the output CCS variables if */
/* needed here. It is also important to make sure that the */
/* correct ICD-10-PCS procedure variable names from your SAS */
/* file are used in the arrays 'PRS'. ICD version code */
/* DXVER or PRVER takes a value of either 9 or 10 (for ICD-9-CM or */
/* ICD-10-CM/PCS) */
/************************************************** ***********************/

%Macro SingleCCS;
/*Look for PRVER variable in input dataset and set macro variable &PRVER accordingly*/
%let dsid = %sysfunc(open(IN1.&CORE));
%if (&dsid) %then %do;
%if %sysfunc(varnum(&dsid,PRVER)) %then %let PRVER = 1;
%else %let PRVER = 0 ;;
%let rc = %sysfunc(close(&dsid));
%end;
%else %let PRVER = 0;;

DATA OUT1.NEW_SINGLE_PCS_CCS (DROP = I);
SET IN1.&CORE;
/************************************************** **/
/* Loop through the PCS procedure array in your */
/* SAS dataset & create the single-level procedure */
/* CCS variables. */
/************************************************** ;*/
%if &NUMPR > 0 %then %do;
ARRAY I10_PRCCS (*) 4 I10_PRCCS1-I10_PRCCS&NUMPR; * Suggested name for ICD-10-PCS Single-Level procedure CCS variables;
ARRAY PRS (*) $ I10_PR1 -I10_PR&NUMPR; * Change ICD-10-PCS procedure variable names to match your file;
%if &PRVER = 1 %then IF PRVER=10 Then ; DO I = 1 TO &NUMPR;
IF Not Missing(PRS(I)) Then I10_PRCCS(I) = INPUT(PRS(I), I10PRCCS.);
END;
%end;

RUN;

PROC PRINT DATA=OUT1.NEW_SINGLE_PCS_CCS (OBS=100);
%if &PRVER = 1 %then %do;
WHERE PRVER=10;
VAR PRVER:
%end;
%if &NUMPR > 0 %then VAR I10_PR1 I10_PRCCS1;;
title2 "Partial Print of the Output ICD-10-PCS Single-Level CCS File";
RUN;
proc freq data = out1.new_single_pcs_ccs;
table i10_prccs1/list missing;
where not missing(i10_pr1);
run;
%Mend SingleCCS;
%SingleCCS;

[Weiwen Ng]

This is a bit of a niche question that might be interesting to people doing health services research with administrative claim or encounter files. These data have ICD-9 or -10 diagnosis or procedure codes that tell the payer (i.e. the health insurance agency covering the patient, e.g. the UK National Health Service, a provincial/territorial implementation of Medicare in Canada, or fee-for-service Medicare in the US) what was wrong with the patient, e.g. the ICD-10 code V95.43XA tells you that this claim deals with injuries from a spacecraft crash (and that this is the initial encounter, as opposed to some sort of subsequent encounter or sequelae of the encounter). For some reason, this is my favorite code because it's a bit far-fetched, and there are others that sound a bit odd at first glance. Rest assured that there are innumerable other codes that describe real diseases.

One US government agency sponsors the Healthcare Cost and Utilization Project (HCUP). One of HCUP's datasets is the National Inpatient Sample (NIS), a national sample of hospital discharges from all payers. In the UK or each Canadian province, there's one central place to go to get data on all the hospital discharges. In the US, things are, shall we say, different. There are innumerable private payers, plus public ones, but the public payers (e.g. Medicare, Medicaid) often outsource coverage to private insurance companies (i.e. Medicare Advantage). So, there's no one place to go to get hospital admission data. You could go to one hospital, but then you have the database from one hospital. You could get Medicare fee-for-service claims, but then you mainly have older adults, some folks with disabilities, and you don't actually have data from about 25% of those groups because they're in Medicare Advantage. Why can't we have nice things?

But I digress, as usual. HCUP has an algorithm that assigns each ICD-9 or -10 code to one or more distinct clinical condition groups. For example, there are a bunch of codes above that deal with spacecraft accidents. You might want to flag everyone in some sort of spacecraft accident, but you do not care if the patient had an accident involving a spacecraft collision, crash, fire, or explosion. The HCUP software described above basically does this. It takes your administrative data, and it merges that data to a massive Excel file. Here are the first few rows of the 2015 file for ICD-9 codes.

* Example generated by -dataex-. To install: ssc install dataex
clear
input str7(icd9cmcode ccscategory) str47 ccscategorydescription str59 icd9cmcodedescription str6 optionalccscategory str43 optionalccscategorydescription
"'01000'" "'1    '" "'Tuberculosis'" "PRIM TB COMPLEX-UNSPEC"   "' '" "' '"
"'01001'" "'1    '" "'Tuberculosis'" "PRIM TB COMPLEX-NO EXAM"  "' '" "' '"
"'01002'" "'1    '" "'Tuberculosis'" "PRIM TB COMPLEX-EXM UNKN" "' '" "' '"
"'01003'" "'1    '" "'Tuberculosis'" "PRIM TB COMPLEX-MICRO DX" "' '" "' '"
"'01004'" "'1    '" "'Tuberculosis'" "PRIM TB COMPLEX-CULT DX"  "' '" "' '"
"'01005'" "'1    '" "'Tuberculosis'" "PRIM TB COMPLEX-HISTO DX" "' '" "' '"
"'01006'" "'1    '" "'Tuberculosis'" "PRIM TB COMPLEX-OTH TEST" "' '" "' '"
"'01010'" "'1    '" "'Tuberculosis'" "PRIM TB PLEURISY-UNSPEC"  "' '" "' '"
"'01011'" "'1    '" "'Tuberculosis'" "PRIM TB PLEURISY-NO EXAM" "' '" "' '"
"'01012'" "'1    '" "'Tuberculosis'" "PRIM TB PLEUR-EXAM UNKN"  "' '" "' '"
"'01013'" "'1    '" "'Tuberculosis'" "PRIM TB PLEURIS-MICRO DX" "' '" "' '"
"'01014'" "'1    '" "'Tuberculosis'" "PRIM TB PLEURISY-CULT DX" "' '" "' '"
"'01015'" "'1    '" "'Tuberculosis'" "PRIM TB PLEURIS-HISTO DX" "' '" "' '"
"'01016'" "'1    '" "'Tuberculosis'" "PRIM TB PLEURIS-OTH TEST" "' '" "' '"
"'01080'" "'1    '" "'Tuberculosis'" "PRIM PROG TB NEC-UNSPEC"  "' '" "' '"
"'01081'" "'1    '" "'Tuberculosis'" "PRIM PROG TB NEC-NO EXAM" "' '" "' '"
"'01082'" "'1    '" "'Tuberculosis'" "PRIM PR TB NEC-EXAM UNKN" "' '" "' '"
"'01083'" "'1    '" "'Tuberculosis'" "PRIM PRG TB NEC-MICRO DX" "' '" "' '"
"'01084'" "'1    '" "'Tuberculosis'" "PRIM PROG TB NEC-CULT DX" "' '" "' '"
"'01085'" "'1    '" "'Tuberculosis'" "PRIM PRG TB NEC-HISTO DX" "' '" "' '"
end

In that file, the second variable, CCS category, is which category the diagnosis code got flagged as. There are well over 200 of these categories for the ICD-9 system. I believe there are at least 500 for ICD-10. It's probably worth noting this: the ICD-9 version and the beta ICD-10 version of this program will return you your original dataset in wide format, so you have 200-500+ variables added to the original data. I'd expect researchers to just select the CCS categories they're interested in, or collapse them by person ID to get some sort of count, or similar operations.

In any case, if you know reshape and merge, it may be more advantageous for you to:

1. Take a copy of your inpatient data with just the diagnosis code fields (claims usually have several fields for all the relevant ICD-9 or -10 codes the patient has) and the claim ID
2. Reshape that into long format (i.e. each claim turns into one or more observations/rows depending on how many DX fields were populated)
3. Merge that with the CCS data (on the value of the ICD-10 diagnosis code)
4. Keep the claim ID, diagnosis code, and the CCSR category (plus the text description if desired)
5. Reshape wide
6. Merge back to your inpatient data
7. Process as needed
8. Repeat for the procedure codes

I know this is complex and that it seems a bit clunky. Actually, if you know an SQL programmer, I believe this type of task is easier to do in SQL.