Summarize data by quarter year and doctor's ID number

[Saskia Bogers]

I have a dataset that contains lab tests performed on a set of patients. Each observation is one test (they're all the same test), performed on one patient.
Variables are: patient ID, patient sex, patient YOB, doctor ID, and quarter (i.e. the quarter year that the test was performed in, with values 1-40, as the data spans 10 years).

I now want to look at the doctors testing behaviour, and produce the following table:

	Median number of tests performed per Doctor	IQR	Range of number of tests performed per doctor (min-max)	Number of doctors who ordered 0 tests	Number of distinct doctors who ordered a test in this quarter
Q1 2011
Q2 2011
Q3 2011
Q4 2011
Q1 2012
...etc

Thus, by quarter and doctor ID i want to look at summary statistics. I have been trying to create these outcomes by using the collapse command, but have had no luck so far.
Can anyone help me on how to write the command to get the results for my table? I then want to make the same tables by sex and age categories, so maybe collapse isn't the fasest way because I would lose my patient sex and YOB data.

Thanks!

[Clyde Schechter]

You have to do a little data manipulation before you can go to -collapse- for this. First, in order to deal with identifying doctors who ordered no tests, you have to assure that there will be an observation in each quarter for every doctor, whether that doctor ordered any tests or not. The -fillin- command does this, and, helpfully, also enables you to identify the observations that were original vs those it created to fill the gaps. Then you can count up the number of tests ordered by each doctor in each quarter using -egen, total()-. Then you reduce the data to one observation per doctor per quarter. Then you can generate dichotomous indicators for no tests ordered and or some tests ordered. Then, you can -collapse- -by(quarter)-.

fillin quarter doctor_id
by quarter doctor_id, sort: egen n_tests_ordered = total(_fillin == 0)
keep quarter doctor_id n_tests_ordered
by quarter doctor_id: keep if _n == 1
gen zero_ordered = (n_tests_orderd == 0)
gen some_ordered = !zero_ordered

collapse (p50) median_orders = n_tests_ordered ///
    (iqr) iqr_orders = n_tests_ordered ///
    (min) min_orders = n_tests_ordered ///
    (max) max_orders = n_tests_ordered ///
    (min) min_orders = n_tests_ordered ///
    (first) zero_ordered some_ordered, by(quarter)

Because no example data was provided, this code is untested and may have errors.

To do the same thing by patient sex and YOB, just -preserve- the data before you start, and then -restore- it to start over. The code is really quite the same, except that wherever the code above mentions quarter, you substitute quarter sex YOB. Voila!

In the future, when you want help with code, please supply example data, and do so using the -dataex- command. If you are running version 17, 16 or a fully updated version 15.1 or 14.2, -dataex- is already part of your official Stata installation. If not, run -ssc install dataex- to get it. Either way, run -help dataex- to read the simple instructions for using it. -dataex- will save you time; it is easier and quicker than typing out tables. It includes complete information about aspects of the data that are often critical to answering your question but cannot be seen from tabular displays or screenshots. It also makes it possible for those who want to help you to create a faithful representation of your example to try out their code, which in turn makes it more likely that their answer will actually work in your data.

[Saskia Bogers]

Hi Clyde, thank you, that worked like a charm and i've learned a new trick! great!
I couldn't provide example data as I'm using STATA on a university server, and it's not allowed to connect to the internet. Thus, any scc install commands (or webuse commands) do not work, unfortunately. But this worked out.