Hi. I have medical prescription data ranging from 2007-2012 in the format below. The two identifying variables drug name (rxname) and year and variables WAC and MMS are in long form. The other variables fall into two categories - "mean_Xyear" and "avg_sum_Xyear" and are in wide form as shown below, where a mean/average variable exists by variable name (variable name X in mean_Xyear) and year (year in mean_Xyear). I want to convert this into a long dataset with the variables rxname, year, WAC, MMS, mean for each variable name, and average for each variable name. So each rxname will have multiple year observations with the corresponding WAC, MMS, mean"X", and average"X" values. In the example below, this will give me the variables rxname, year, WAC MMS, mean_rxsf, mean_rxmr, avg_sum_rxsf, and avg_sum_rxmr. Given that the dataset currently is both in long and wide form, I'm lost on how to do this. Any help would be much appreciated. Thanks.


Code:
input str50 rxname float(year1 WAC MMS mean_rxsf07x mean_rxmr07x avg_sum_rxsf07x avg_sum_rxmr07x mean_rxsf08x mean_rxmr08x avg_sum_rxsf08x avg_sum_rxmr08x)
"ACANYA"                             2011 .018485604  .003963576         .         .         .         .         .         .         .         .
"ACANYA"                             2012 .021359963   .00253045         .         .         .         .         .         .         .         .
"ACANYA"                             2009 .014532895           0         .         .         .         .         .         .         .         .
"ACANYA"                             2010 .015220596  .005711484         .         .         .         .         .         .         .         .
"ACCOLATE"                           2009 .007250024   .04813249         .         .         .         .         .         .         .         .
"ACCOLATE"                           2007 .006417863    .0299569  6.366267  30.67493  32.81027 227.72614         .         .         .         .
"ACCOLATE"                           2008 .006612917   .04954252         .         .         .         .  30.20035  58.13281 209.11227  426.0211
"ACCOLATE"                           2010 .007608728   .07854433         .         .         .         .         .         .         .         .
"ACIPHEX"                            2010  .02838141    .0439065         .         .         .         .         .         .         .         .
"ACIPHEX"                            2011 .032204468   .05776187         .         .         .         .         .         .         .         .
"ACIPHEX"                            2009  .02563298  .011431296         .         .         .         .         .         .         .         .
"ACIPHEX"                            2012 .036587633   .05204981         .         .         .         .         .         .         .         .
"ACIPHEX"                            2008  .02276266  .008661115         .         .         .         .  52.83787  23.49355 437.15915 157.95343
"ACIPHEX"                            2007  .02172653 .0039337035  56.37726  38.18855  417.8715 209.04803         .         .         .         .
"ACIPHEX (10X10 BLISTER PACK)"       2007  .02172653 .0039337035        58     81.87       174    245.61         .         .         .         .
"ACTIGALL"                           2008 .018465104  .018614486         .         .         .         .        10         0        34         0
"ACTIGALL"                           2009  .02023306    .0178034         .         .         .         .         .         .         .         .
"ACTONEL"                            2008  .08477159  .014807846         .         .         .         .  53.21417  30.13887  341.4192  199.2863
"ACTONEL"                            2009   .1170634  .016826285         .         .         .         .         .         .         .         .
"ACTONEL"                            2012   .1963083  .014222048         .         .         .         .         .         .         .         .
"ACTONEL"                            2007 .073792845  .010604203  41.97368  44.12427 286.98135  388.0258         .         .         .         .
"ACTONEL"                            2010   .1553246   .02109581         .         .         .         .         .         .         .         .
"ACTONEL"                            2011   .1746607  .021670034         .         .         .         .         .         .         .         .
"ACTONEL (DOSE PACK,FILM-COATED)"    2008  .08477159  .014807846         .         .         .         . 69.724106 34.163334  444.1323  113.6677
"ACTONEL (DOSEPACK,FILM-COATED)"     2008  .08477159  .014807846         .         .         .         .   20.1199  48.52158  158.3146  405.3759
"ACTONEL (DOSEPACK,FILM-COATED)"     2007 .073792845  .010604203 35.413296  50.65252  304.4067   408.858         .         .         .         .
"ACTONEL (FILM-COATED)"              2007 .073792845  .010604203   40.0492   58.3136  235.1612  273.1592         .         .         .         .
"ACTONEL (FILM-COATED)"              2008  .08477159  .014807846         .         .         .         .  45.19429 64.043144 298.83542  365.5293
"ACTONEL 12 PK"                      2009   .1170634  .016826285         .         .         .         .         .         .         .         .
"ACTONEL 12PK"                       2009   .1170634  .016826285         .         .         .         .         .         .         .         .
"ACTONEL 4PK"                        2009   .1170634  .016826285         .         .         .         .         .         .         .         .
"ACTONEL PACK"                       2009   .1170634  .016826285         .         .         .         .         .         .         .         .
"ACTONEL WITH"                       2011   .1746607  .021670034         .         .         .         .         .         .         .         .
"ACTONEL WITH CALCIUM"               2009   .1170634  .016826285         .         .         .         .         .         .         .         .
"ADVAIR"                             2009  .01578993    .0778434         .         .         .         .         .         .         .         .
"ADVAIR"                             2007 .014288618   .06255793 127.82266  7.626766  808.9195  53.98466         .         .         .         .
"ADVAIR"                             2008  .01468538   .07563292         .         .         .         .  34.05247  4.366654  181.1845  10.84863
"ADVAIR"                             2010 .016395696    .1035218         .         .         .         .         .         .         .         .
"ADVAIR"                             2011 .017487908    .1153965         .         .         .         .         .         .         .         .
"ADVAIR"                             2012 .018821985   .11336165         .         .         .         .         .         .         .         .
"ADVAIR 250/50"                      2008  .01468538   .07563292         .         .         .         . 3.9894736  57.47053 21.610527 344.82315
"ADVAIR 250/50"                      2012 .018821985   .11336165         .         .         .         .         .         .         .         .
"ADVAIR 250/50"                      2007 .014288618   .06255793 26.880676   23.1873   161.164  225.9937         .         .         .         .
"ADVAIR 500/50"                      2008  .01468538   .07563292         .         .         .         .       3.1 299.10306 35.717392  3435.917
"ADVAIR DISK"                        2007 .014288618   .06255793   84.6507         0  631.6146         0         .         .         .         .
"ADVAIR DISK"                        2011 .017487908    .1153965         .         .         .         .         .         .         .         .
"ADVAIR DISKI"                       2009  .01578993    .0778434         .         .         .         .         .         .         .         .
"ADVAIR DISKU"                       2012 .018821985   .11336165         .         .         .         .         .         .         .         .
"ADVAIR DISKU"                       2011 .017487908    .1153965         .         .         .         .         .         .         .         .
"ADVAIR DISKU"                       2009  .01578993    .0778434         .         .         .         .         .         .         .         .
"ADVAIR DISKU"                       2007 .014288618   .06255793  58.56229 120.19028 194.78885  577.1854         .         .         .         .
"ADVAIR DISKU"                       2008  .01468538   .07563292         .         .         .         .        25         0        25         0
"ADVAIR DISKU"                       2010 .016395696    .1035218         .         .         .         .         .         .         .         .
"ADVAIR DISKU 250/50"                2008  .01468538   .07563292         .         .         .         .      1.55    272.04       6.2   1088.16
"ADVAIR DISKUC"                      2009  .01578993    .0778434         .         .         .         .         .         .         .         .
"ADVAIR DISKUS"                      2012 .018821985   .11336165         .         .         .         .         .         .         .         .
"ADVAIR DISKUS"                      2011 .017487908    .1153965         .         .         .         .         .         .         .         .
"ADVAIR DISKUS"                      2007 .014288618   .06255793 27.092876  44.50548  175.5737 299.60522         .         .         .         .
"ADVAIR DISKUS"                      2008  .01468538   .07563292         .         .         .         .  38.26122 17.944082  182.3249 35.888165
"ADVAIR DISKUS"                      2009  .01578993    .0778434         .         .         .         .         .         .         .         .
"ADVAIR DISKUS"                      2010 .016395696    .1035218         .         .         .         .         .         .         .         .
"ADVAIR DISKUS 100/50"               2008  .01468538   .07563292         .         .         .         . 31.672966  42.26171 245.20833  346.0746
"ADVAIR DISKUS 100/50"               2007 .014288618   .06255793  30.64029  48.54159  243.2401  335.0289         .         .         .         .
"ADVAIR DISKUS 60'S"                 2010 .016395696    .1035218         .         .         .         .         .         .         .         .
"ADVAIR DISKUS INHALER"              2010 .016395696    .1035218         .         .         .         .         .         .