Strange Results When Dividing Numeric Values

[Mahmoud Amoli]

Dear Statlist community,

I faced a strange issue working with the following dataset when I changed a string variable to numeric and wanted to create a new variable by dividing: let's look at my codes and data below. The problem is that my new variable (visit_ratio) is simply wrong, where in fact, it should be a simple arithmetic. Any help would be appreciated.

*count number of observation per year and state
bysort year state_name : gen visit = _n

*create a numerical value for number of physican
encode Number_of_Active_Physicians, gen(num_phy)

*create a ratio
gen visit_ratio = visit/num_phy

and here is the data:

* Example generated by -dataex-. For more info, type help dataex
clear
input int year str20 state_name str27 Number_of_Active_Physicians
2000 "alaska" "1,764"
2013 "alaska" "1,764"
2013 "alaska" "1,764"
2014 "alaska" "1,764"
2014 "alaska" "1,764"
2014 "alaska" "1,764"
2014 "alaska" "1,764"
2015 "alaska" "1,764"
2017 "alaska" "1,764"
2017 "alaska" "1,764"
end

The visit ratio should be in decimal format, and I got confused why I cannot obtain the right results. Thanks.

[Mike Murphy]

The issue is that encode creates a labelled categorical variable. So the data shows as having the label "1,764" but has the value "1", which you can confirm by running:

tab num_phy, nolab

To resolve, replace the second line with:

destring Number_of_Active_Physicians, gen(num_phy)

[Mahmoud Amoli]

The issue is that encode creates a labelled categorical variable. So the data shows as having the label "1,764" but has the value "1", which you can confirm by running:

tab num_phy, nolab

To resolve, replace the second line with:

destring Number_of_Active_Physicians, gen(num_phy)

Thanks for your response Mike, but running your codes gives me the following error message:

"Number_of_Active_Physicians: contains nonnumeric characters; no generate"

What do you suggest I should do? Thanks.

[Mike Murphy]

Ah, sorry destring can't handle the comma. Just add:

replace Number_of_Active_Physicians = subinstr(Number_of_Active_Physicians,",","",.)

in the line above the destring command

[Mike Murphy]

Note as well that _n gives you a running number for the order of observations. I'm not sure exactly what you're calculating, but I suspect you want to use _N or collapse the data in some way

[Mahmoud Amoli]

Note as well that _n gives you a running number for the order of observations. I'm not sure exactly what you're calculating, but I suspect you want to use _N or collapse the data in some way

Thank you so much Mike, the issue is resolved, and I appreciate your last comment as well.