st: RE: Populating variables based upon integer value in the observation

["MacLennan, Graeme" <g.maclennan@abdn.ac.uk>]

Mathew, I think -expand- then -reshape- might do what you want, see the following with some lists at each step to see what is happening.  You might need to rename your variables to suit, but this will work.
Regards, and HTH,
Graeme (MacLennan).

*** begin example ***
webuse stackxmpl, clear
drop c d
list
gen id = _n
expand a
sort id
list
bysort id: gen yr = _n
list
reshape wide b, i(id) j(yr)
foreach b of varlist b* {
        replace `b' = 0 if `b' == .
        }
list
*** end example ***

-----Original Message-----
From: owner-statalist@hsphsun2.harvard.edu [mailto:owner-statalist@hsphsun2.harvard.edu] On Behalf Of Mathew Wilkins
Sent: 24 July 2012 11:25
To: statalist
Subject: st: Populating variables based upon integer value in the observation


Dear Stata listers,
I am wondering if it is possible to populate a certain number of variables based upon san integer variable in the observation.  I am still relatively new to stata and would relatively easily be able to program this in Access or Excel.


I have variables a, b and yr1-yr10 (all integer)


I would like some commands that will populate variable b into variables yr1-yr10, the number of the variables yr1-yr10 that are populated is based upon variable a.  The below example dataset show an example of what I would like to populate

    obs |  a  |   b  |  yr |  yr2 |  yr |  yr4  |  yr5 |  yr6 |  yr7 |  yr8 |  yr |  yr1
        |     |      |  1  |      |  3  |       |      |      |      |      |  9  |   0
  ------+-----+------+-----+------+-----+-------+------+------+------+------+-----+------
     1  |  4  |  25  |  25 |  25  |  25 |   25  |   0  |   0  |   0  |   0  |  0  |   0
  ------+-----+------+-----+------+-----+-------+------+------+------+------+-----+------
     2  |  10 |   3  |  3  |   3  |  3  |   3   |   3  |   3  |   3  |   3  |  3  |   3
  ------+-----+------+-----+------+-----+-------+------+------+------+------+-----+------
     3  |  7  |  19  |  19 |  19  |  19 |   19  |  19  |  19  |  19  |   0  |  0  |   0
  ------+-----+------+-----+------+-----+-------+------+------+------+------+-----+------
     4  |  1  |  14  |  14 |   0  |  0  |   0   |   0  |   0  |   0  |   0  |  0  |   0
  ------+-----+------+-----+------+-----+-------+------+------+------+------+-----+------
     5  |  9  |   5  |  5  |   5  |  5  |   5   |   5  |   5  |   5  |   5  |  5  |   0
  ------+-----+------+-----+------+-----+-------+------+------+------+------+-----+------
     6  |  8  |  79  |  79 |  79  |  79 |   79  |  79  |  79  |  79  |  79  |  0  |   0









I have managed to use a forvalues loop to create the variables yr1-yr10 and another loop to then populate them.


forvalues x = 1/10 {
gen yr`x' = 0
                if `x'<= a {
                        replace yr`x' = b
                }
}

However the yr columns that are being populated are only based on variable a in the first observation (result set below).

a       b       yr1     yr2     yr3     yr4     yr5     yr6     yr7     yr8     yr9     yr10
4       25      25      25      25      25      0       0       0       0       0       0
10      3       3       3       3       3       0       0       0       0       0       0
7       19      19      19      19      19      0       0       0       0       0       0
1       14      14      14      14      14      0       0       0       0       0       0
9       5       5       5       5       5       0       0       0       0       0       0
8       79      79      79      79      79      0       0       0       0       0       0

I have tried to use an additional forvalues loop for the observation however this either does not work or take too long to process (the full scale dataset I am working on is approximately 11,000 observations and the yr columns go from 1 – 30.

Many thanks in advance for your time and consideration.

Regards
Mathew Wilkins
United Nations Climate Change Secretariat (UNFCCC)

Mathew Wilkins
Statistical Assistant
Strategy & Policy Development

(Embedded image moved to file: pic02220.gif)

United Nations
Climate Change Secretariat
Martin-Luther-King Strasse 8
53175 Bonn, Germany

Phone +49 228 815 1864
Fax +49 228 815 1999
mwilkins@unfccc.int
unfccc.int


The University of Aberdeen is a charity registered in Scotland, No SC013683.

*
*   For searches and help try:
*   http://www.stata.com/help.cgi?search
*   http://www.stata.com/support/statalist/faq
*   http://www.ats.ucla.edu/stat/stata/