Expand a variable into 3 variables, keeping pieces of the first variable in the original variable, and moving other pieces the new variables

[Elle Cooper]

Hello,

I have a variable, primarycarephysicianstandard, and several variables like it, that have several observations, which I want to pull apart into separate variables, while still keeping some part of the variables as it currently exists. Specificially, the variable tabulates as:


$10
$10 copay before deductible
$10 copay before deductible and 15% coinsurance after deductible
$15 copay after deductible
$30 copay and 20% coinsurance after deductible
30%
No charge after deductible

*Note: the variable continues in this fashion with different numbers, and there are several variables like this one*

What I want to do is split the variable up into three variables so that it looks something like the following:

Each observation will have one of these rows

Primarycarephysicianstandard (original variable)	Primarycarephysician Dollar (new variable)	Primarycarephsyciain Percent (new variable)
Dollar amount	10
Dollar copay before deductible	10
Dollar copay before deductible and percent coinsurance after deductible	10	15
Dollar copay after deductible	15
Dollar copay and percent coinsurance after deductible	30	30
Percent		30
No charge after deductible
No charge

For example, if observation 1 has a $115 dollar copay before deductible and 15% coinsurance after deductible and observation 2 has a $20 copay after deductible, the observation list for this variable would like like the following:

Obs	Primarycarephysician	Primarycarephysician dollar	Primarycarephysician percent
1	Dollar copay before deductible and percent coinsurance after deductible	115	15
2	Dollar copay after deductible	20

I have tried using 'split' and concatenating the pieces I need back together, which does not work because the text does not line up using spaces, and I cannot figure out how to add characters to delimit the text the way I want. I have also tried 'regexm' and 'generate' and 'replace' for the new variables.

Any help on this would be so very much appreciated!

[Clyde Schechter]

What you are attempting to do is at once tantalizingly simple appearing, yet probably really quite complicated. To the extent that the second and third variables are obtained just by extracting numbers from the original variable, you should be able to at least get close using regular expressions. It might be helpful to show us exactly what code you've already tried and showing us what was unsatisfactory about the results. If the original variable values are as regular appearing as what you show, I would think that the only tricky part would be deciding which of the new variables gets the number when there is only one.

On the other hand, it can take a long time to iron out all of the wrinkles in the code. It is often simpler and faster to simply create a new data set containing only the distinct variables of the original variable, and then hand edit it to create the three new variables. Then you merge that back into the data set you started with.