Announcement

Collapse
No announcement yet.
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Creating new variables using values of existing variable

    Hi all,

    In a bit of a quandary.

    I have an individual-zip-code-level dataset. Each zip-code has a longitude/latitude associated with it. I want to create variables that hold the value of each first longitude/latitude, second longitude/latitude etc. The maximum number of longitude/latitudes associated with a given person is 220. So ideally, I would have 220 variables, with values missing in cases where the person doesn't have 220 different longitude/latitudes.

    Can someone offer advice on the best way to do so?

    Thanks!

    EDIT: In essence, I want to transpose only the variable containing the longitude/latitude values
    Last edited by Yevgeniy Feyman; 16 Aug 2017, 12:52.

  • #2
    I think perhaps the reshape wide command does what you need. No way to be sure, though, because it's impossible to know from your description what your data is like, or precisely what you intend as the outcome.

    Please review the Statalist FAQ linked to from the top of the page, as well as from the Advice on Posting link on the page you used to create your post, looking especially at sections 9-12 on how to best pose your question. It would be particularly helpful to post a small hand-made example, with just a few variables and observations, showing the data before the process and how you expect it to look after the process. In particular, please read FAQ #12 and use dataex and CODE delimiters when posting to Statalist.

    The more you help others understand your problem, the more likely others are to be able to help you solve your problem.

    Comment


    • #3
      Thanks for the response. I'm happy to clarify.

      Below is the dataex output for a few observations.

      Code:
      * Example generated by -dataex-. To install: ssc install dataex
      clear
      input long npi str5(zip zcta) float(lat longit)
      1003000126 "20166" "20166"  38.98614   -77.4557
      1003000126 "22205" "22205"   38.8835  -77.13953
      1003000126 "24153" "24153" 37.302505  -80.11433
      1003000134 "60201" "60201"  42.05621  -87.69241
      1003000142 "43608" "43608"  41.67989  -83.52855
      end
      My intended outcome is to have as many new variables as necessary, such that each NPI-ZCTA combination's longitude/latitude is stored in a new variable. (I will combine the two into a single string variable)

      Thanks again.

      Comment


      • #4
        Thank you for providing sample data and for using dataex to do so. Starting with the sample data, the following does what you want, I think.
        Code:
        bysort npi (zcta): generate jvar = _n
        reshape wide zip zcta lat longit, i(npi) j(jvar)
        Code:
        . list
        
             +-----------------------------------------------------------------------------------------+
          1. |        npi |  zip1 | zcta1 |     lat1 |   longit1 |  zip2 | zcta2 |    lat2 |   longit2 |
             | 1003000126 | 20166 | 20166 | 38.98614 |  -77.4557 | 22205 | 22205 | 38.8835 | -77.13953 |
             |-----------------------------------------------------------------------------------------|
             |        zip3       |       zcta3        |           lat3        |         longit3        |
             |       24153       |       24153        |       37.30251        |       -80.11433        |
             +-----------------------------------------------------------------------------------------+
        
             +-----------------------------------------------------------------------------------------+
          2. |        npi |  zip1 | zcta1 |     lat1 |   longit1 |  zip2 | zcta2 |    lat2 |   longit2 |
             | 1003000134 | 60201 | 60201 | 42.05621 | -87.69241 |       |       |       . |         . |
             |-----------------------------------------------------------------------------------------|
             |        zip3       |       zcta3        |           lat3        |         longit3        |
             |                   |                    |              .        |               .        |
             +-----------------------------------------------------------------------------------------+
        
             +-----------------------------------------------------------------------------------------+
          3. |        npi |  zip1 | zcta1 |     lat1 |   longit1 |  zip2 | zcta2 |    lat2 |   longit2 |
             | 1003000142 | 43608 | 43608 | 41.67989 | -83.52855 |       |       |       . |         . |
             |-----------------------------------------------------------------------------------------|
             |        zip3       |       zcta3        |           lat3        |         longit3        |
             |                   |                    |              .        |               .        |
             +-----------------------------------------------------------------------------------------+

        Comment


        • #5
          Great, it did exactly what I needed! Thanks for your help.

          Comment

          Working...
          X