tab and cross tab options when working with claims data

[Maliha Ali]

I am working with Medicaid claims data where the data is organized as such: each patient's claims are listed by order of the patient's study id. Even though there are 50 patients, the claims episodes are 110,000. How can I get the output of a tab command to display the frequencies by patient, instead of the frequencies of the claims? Looked everywhere but could not find this.

[Clyde Schechter]

You need to create a new variable that identifies a unique observation from among each patient's many observations, Then you can do your tabulation conditioned on that variable. So like this:

egen byte flag = tag(study_id)
tab date_of_birth if flag

This approach only works correctly for variables that are unchanging attributes of the patient and do not vary from one claim to the next. If you are trying to tabulate things like "how many patients ever had a claim with a principal diagnosis of diabetes mellitus" then it's a tad more complicated:

by study_id, sort: egen had_claim_for_dm = max(principal_diagnosis == "diabetes mellitus")
tab had_claim_for_dm if flag

Of course, in your real data, your principal_diagnosis is probably an ICD9 or ICD10 code, and perhaps several different codes would have to be considered. So you would modify the -principal_diagnosis == "diabetes mellitus"- part accordingly. (Don't forget the -inlist()- function if multiple codes apply to a single condition.)

[Maliha Ali]

Thanks so much! It works on a simple tab. What if I want to use this for cross tabs and bivariate associations?

[Clyde Schechter]

What if I want to use this for cross tabs and bivariate associations?

It will work equally well.

tab var1 var2 if flag
corr var3 var4 if flag

It seems as if you are engaged in a fairly substantial project here but have little familiarity with Stata. That's fine, we were all beginners at one time. Before plunging ahead directionless on a big project, I recommend you step back and first learn the Stata basics. Open up the PDF manuals that come installed with your Stata. (Launch Stata, click on Help and select PDF Documentation.) Read from beginning to end the Getting Started [GS] and User's Guide [U] volumes. It's a lot of reading. But it will acquaint you with the way Stata approaches data management and analysis, and it will introduce you to the basic commands that every Stata user needs day in and day out. You won't remember every detail, but in future situations you will usually recognize what commands are likely to be helpful, and then you can consult those commands' help files or manual chapters for the details. The time you invest in this will soon be amply repaid.

[Maliha Ali]

Thanks Clyde. I was using the 'if flag' in the options while doing the cross tabs. Thank you also your advice. I have been attending to the GS and U manuals and they have been most helpful. I'm working on my dissertation and the data analysis is complicated because its the first time I'm handling claims data, and lots of counts and new formulas to create and calculate.