Re: st: -reshape-, or "is there an easier way to reshape my datathan how I am doing it?"

[John Fulton <fulton@ssc.wisc.edu>]

Maybe I misunderstand the question but this is doable by collapse:

collapse (max) gbm-gwf,by(year fip age)

assuming you're going from this:
     +--------------------------------------------------------+
     | year   fip   age       gbm       gbf      gwm      gwf |
     |--------------------------------------------------------|
  1. | 1983     1    15    data        .        .        . |
  2. | 1983     1    15         .    data        .        . |
  3. | 1983     1    15         .         .    data        . |
  4. | 1983     1    15         .         .        .      data |
to this:
   +---------------------------------------------------------------+
     | year   fip   age       gbm       gbf      gwm      gwf     |
     |----------------------------------------------------------------|
  1. | 1983     1    15    data        data      data      data  |

John.

Scott Cunningham wrote:
> I'm having a hard time figuring out how to use reshape with the data 
> in the form I currently have it in.
>
> DATA:  1982-2004 CDC data on gonorrhea rates by age cohort, race, sex 
> and state
>
>       +--------------------------------------------+
>        | year   fip   race   sex        gr   age |
>        |---------------------------------------------|
>
> where:
>
> ------------------------------------------------------------------------------- 
> year            int    %8.0g                  YEAR
> fip             byte   %8.0g                  State FIPS codes
> race            byte   %8.0g                  W=1, B=2
> sex             byte   %8.0g                  M=1, F=2
> gr              float  %9.0g                  Gonorrhea Rate (per 
> 100,000)
> age             float  %9.0g                  Age Cohort
> ------------------------------------------------------------------------------- 
>
> I need to use -reshape- to transform the data into a format that will 
> allow me to estimate various panel models.  I am going to merge other 
> demographic data to this dataset, but that I can do.  What I cannot do 
> is figure out how to get -reshape- to work here.  When I simply use 
> -reshape-, I am told that I have fip/year that do not uniquely 
> identify.  I know this, of course.  So, I thought that what I could do 
> was create a race, gender and state specific gonorrhea rate.  So I did 
> that.  It looked like this:
>
>      +-------------------------------------------------------------+
>       | year   fip   age       gbm       gbf      gwm      gwf |
>       |--------------------------------------------------------------|
>
> where gbm is "gonorrhea rates for black males," gbf for black females, 
> gwm for white males, and gwf for white females.  I then -preserve- the 
> data, keep only the one of the age cohorts (15-19 year olds 
> initially), drop the age variable, and then try to -reshape- again.  
> But this doesn't work, because I have four fip observations for every 
> year, that looks like this:
>
>       +--------------------------------------------------------+
>       | year   fip   age       gbm       gbf      gwm      gwf |
>       |--------------------------------------------------------|
>    1. | 1983     1    15    data        .        .        . |
>    2. | 1983     1    15         .    data        .        . |
>    3. | 1983     1    15         .         .    data        . |
>    4. | 1983     1    15         .         .        .      data |
>
> I need to collapse these observations so that it looks like this:
>
>       +---------------------------------------------------------------+
>       | year   fip   age       gbm       gbf      gwm      gwf     |
>       |----------------------------------------------------------------|
>    1. | 1983     1    15    data        data      data      data  |
>
> But neither -fillin- nor -collapse- appear to do the trick.
>
> MY QUESTION:  How do I collapse the data like the above?  Secondly, is 
> there a more efficient way to take my data in its original format and 
> create a panel without having to create 9 different age-specific 
> panels which I will merge later?*
> *   For searches and help try:
> *   http://www.stata.com/support/faqs/res/findit.html
> *   http://www.stata.com/support/statalist/faq
> *   http://www.ats.ucla.edu/stat/stata/
*
*   For searches and help try:
*   http://www.stata.com/support/faqs/res/findit.html
*   http://www.stata.com/support/statalist/faq
*   http://www.ats.ucla.edu/stat/stata/