How to create multiple files from a single file programmatically
<p>I have a dataset with observations about households; within each household there are individuals. The number of individuals per household differs. Households are identified with an <code>id</code> and members of the household are identified according to the order they were interviewed. So if household 1 had 4 members, the variable <code>id</code> is the same across all of them, but variable <code>order</code> goes from 1 to 4. The problem I have is that, for some variables, only the first member of the household answered for the rest of the members; therefore I have a mixture of long and wide format within my dataset.</p>

<p>What I need to do is to assign to the correspondent members of the household the values that were answered by the first member of the household. To explain further the structure of my data I´ll give the following toy example:</p>

<pre><code>clear
input    ///
    id order age o_a1 o_a2  v1a1 v1a2  v2a1   v2a2  o_b1 o_b2  v1b1 v1b2 v2b1 v2b2 
      1  1   54    1     .   50    .    100      .   .     .     .    .    .    .
      1  2   50    .     .    .    .      .      .   .     .     .    .    .    .
      1  3   27    .     .    .    .      .      .   .     .     .    .    .    .
      1  4   18    .     .    .    .      .      .   .     .     .    .    .    .
      2  1   60    3     4   70   23     10     15   2     5    80   90  100  140
      2  2   72    .     .    .    .      .      .   .     .     .    .    .    .
      2  3   58    .     .    .    .      .      .   .     .     .    .    .    .
      2  4   20    .     .    .    .      .      .   .     .     .    .    .    .
      2  5   23    .     .    .    .      .      .   .     .     .    .    .    .
end
</code></pre>

<p>In the toy example from above, I have a household level variable <code>id</code> and  individual level variables: <code>order</code> corresponds to the order of the individual in the household; <code>age</code> is their age. The other variables correspond to debts. A  household can report at most two debts for each type of debt. In this case there are two types of debt, debt <code>a</code> or <code>b</code>'.</p>

<p><code>o_a1</code> gives the order of the member of the household with the first debt of type <code>a</code>. If we look at row 5 in the dataset, <code>o_a1</code> is 3, meaning that the individual of the household with that debt is the third individual of the household, that is row 7, the one aged 58. Similarly, <code>o_a2</code> indicates the order of the individual with the second debt.</p>

<p><code>v1a</code> and <code>v2a</code> correspond to the first variable of debt <code>a</code>, for example size of debt in dollars. This means that member 3 of household 2 would be in debt for $70 and individual 4 of household 2 would be in debt for $23. Variables <code>v2a1</code> and <code>v2a2</code> correspond to a second variable of the debt and so on.</p>

<p>Then we have another type of debt, debt <code>b</code>, and the logic is the same as before.</p>

<p>In reality the data contains many variables for each debt, and many more types of debt (educational, housing, credit, credit cards, etc), as I´m not still sure which debts I´m going to study yet, I want to store the information from each type of debt in different datasets, and then <code>merge</code> the data of my interest using the variables <code>id</code> and <code>order</code> as identifiers. So I want to have a table for each debt, and keep the variables of the individual (in this case their age) in other tables as well. In the actual dataset other variables include sex, educational level, etc. </p>

<p>I already managed to do this for a couple of debts, but since there are many, I would like to know if there is a way to do this programmatically.</p>

<p>I´ll show what I did for each type of debt.</p>

<p>1) I kept the variables for a certain debt and only for <code>order == 1</code>. In the case of debt <code>a</code>, I only kept variables <code>o_a1 o_a2  v1a1 v1a2  v2a1   v2a2</code> and the identifiers <code>id</code> and <code>order</code>.</p>

<pre><code>drop age
keep if order==1
keep id order *a*
</code></pre>

<p>2) I <code>reshape</code>d the data from wide to long to obtain the order of the individual of each household in long format so that each debt was in the row with its correspondent debtor.</p>

<pre><code>reshape long o_a , i(id) j(ncred)
</code></pre>

<p>3) I <code>save</code>d the <code>reshape</code>d data in a new file.</p>

<pre><code>save "debt_a.dta", replace
</code></pre>

<p>4) I created a dataset for each credit of type <code>a</code>.</p>

<p>4.1) I created a dataset for debt <code>a1</code> and <code>drop</code>ped the observations that were missing in the new created variable <code>o_a</code>.</p>

<pre><code>use "debt_a.dta", clear
drop if o_a  == .  
</code></pre>

<p>Next, I <code>drop</code>ped the variables corresponding to debt <code>a2</code>, and only kept the rows belonging to credit <code>a1</code> (<code>ncred == 1</code>).</p>

<pre><code>drop *a2
keep if ncred==1
</code></pre>

<p>To be able in step 5 to append the dataset of debt <code>a1</code> and debt <code>a2</code>, I erased the substring <code>a1</code> from <code>a1</code> debt variables. I did the same in step 4.2. for debt <code>a2</code>.</p>

<pre><code>foreach var of varlist * {
    local newname : subinstr local var "a1" "", all
    if "`newname'" != "`var'" {
           rename `var' `newname'
    }
}
save "debt_a1.dta", replace
</code></pre>

<p>4.2) The same as step 4.1 but for debt <code>a2</code>.</p>

<pre><code>use "debt_a.dta", clear
drop if o_a  == .  
drop *a1
keep if ncred==2

foreach var of varlist * {
    local newname : subinstr local var "a2" "", all
    if "`newname'" != "`var'" {
        rename `var' `newname'
    }
}
save "debt_a2.dta", replace
</code></pre>

<p>5) Then I <code>append</code>ed both datasets.</p>

<pre><code>use "debt_a1.dta", clear 
append using "debt_a2.dta"
drop ncred 
replace order = o_a 
drop o_a
sort id order
save "debts_a_long.dta", replace
</code></pre>

<p>So I ended with the following dataset:</p>

<pre><code>id  order   v1  v2
 1  1   50  100
 2  3   70  10
 2  4   23  15
</code></pre>

<p>Therefore, now I can <code>merge</code> the individual debt data with other tables. Let's assume that the individual data table looked like this:</p>

<pre><code>clear 
input ///
  id order age sex years_education
   1  1   54   1     12
   1  2   50   1     14 
   1  3   27   0      8
   1  4   18   1     12 
   2  1   60   0      6
   2  2   72   1      8
   2  3   58   0     12
   2  4   20   0     14
   2  5   23   1     17
end
save "individual.dta", replace
</code></pre>

<p>Hence, instead of having information only on age, I also have on sex and years of education.</p>

<p>Now I can <code>merge</code> the debt data with the individual 'sociodemographic' data. In this case, the <code>merge</code> corresponds to a left join in SQL.</p>

<pre><code>use "debts_a_long.dta", clear
joinby id order  using "individual.dta"
</code></pre>

<p>Which ends in:</p>

<pre><code>id  order   v1  v2  age sex years_education
1   1   50  100 54  1   12
2   3   70  10  58  0   12
2   4   23  15  20  0   14
</code></pre>

<p>That is why I want to create a table for each type of debt. </p>

<p>Is there a way to do this programmatically in Stata for each debt instead of writing the code several times? </p>