Error in reshaping data

[pravin chaudhari]

* Example generated by -dataex-. For more info, type help dataex
clear
input float qdate double permno str5 fund_id float delta1
160 77403 "f1" .3287323
160 80446 "f1" .8991209
160 81285 "f207" -.05502164
160 70033 "f38" .017314212
160 11308 "f33" -.004834611
160 81285 "f126" .4134931
160 63503 "f171" .
160 81285 "f152" .07659303
160 20117 "f55" .004936062
160 83653 "f31" .
160 77292 "f5" .003238792
160 85786 "f65" -.8396007
160 82656 "f222" .
160 49154 "f78" .024015393
160 45794 "f50" .010397288
160 62907 "f276" .
160 88313 "f80" .005926885
160 24969 "f67" .
160 75049 "f36" -.001889718
160 . "f397" -.00025834295
160 79057 "f60" -.7634864
160 75049 "f36" -.0045131226
160 46340 "f244" .10832843
160 53866 "f420" -.06625882
160 40539 "f386" .01007914
160 59408 "f298" .010570597
160 76129 "f40" .06556233
160 . "f6" -6.376266e-06
160 86339 "f212" .09723815
160 76837 "f8" .
160 40539 "f14" .002620869
160 56223 "f116" -.06658934
160 21979 "f389" .0009690407
160 66800 "f24" -.00024742456
160 87717 "f163" .009579417
160 87639 "f50" -.9310441
160 81128 "f168" -.9776711
160 17806 "f12" -.3873903
160 45495 "f108" -.06576084
160 84785 "f216" -.6135679
160 34833 "f474" .11734488
160 81877 "f64" -.024208035
160 80055 "f33" .0002757504
160 83934 "f5" .
160 58975 "f171" .3181729
160 86238 "f33" -.0001858864
160 11976 "f161" -.003807765
160 77392 "f139" -.5651348
160 10516 "f14" -.05125819
160 . "f19" -.00004626829
160 78034 "f98" .04130717
160 12490 "f16" -.00010524283
160 23536 "f9" .13085961
160 62034 "f488" -.00357858
160 12079 "f3" .021265937
160 . "f3" -1.5888531e-08
160 83520 "f236" -.3720391
160 . "f177" .00001225003
160 . "f171" .0001438698
160 12052 "f79" .00121424
160 38156 "f9" -.00219303
160 43449 "f508" .1249842
160 86144 "f220" -.32730085
160 86238 "f33" .00009136245
160 76185 "f131" .
160 42200 "f3" .013238617
160 87660 "f136" -.03858229
160 81172 "f168" .
160 84168 "f388" -.2512541
160 10562 "f142" -.01524321
160 79799 "f82" -.00018448697
160 66093 "f44" .018792
160 86717 "f344" .
160 82605 "f256" .7679914
160 22509 "f162" -.019645983
160 48960 "f268" -.7268059
160 21573 "f278" .07175231
160 . "f242" .0000618167
160 86946 "f64" .04944932
160 79323 "f20" -.0015609075
160 81290 "f7" .0912681
160 83333 "f116" -.3039297
160 50948 "f187" -.03959715
160 81262 "f321" .
160 38703 "f45" -.0001941003
160 76568 "f73" .
160 77582 "f412" -.9870219
160 80362 "f108" -.05593323
160 63830 "f7" -.1812483
160 82775 "f509" -.10962739
160 75049 "f36" -.0009406946
160 24264 "f3" .015144164
160 75049 "f36" .00011288052
160 18729 "f84" .005288623
160 37955 "f119" .7634225
160 85495 "f70" .002672847
160 76201 "f42" .0029007844
160 83621 "f88" .
160 70519 "f164" .
160 85888 "f361" .0019455864
end
format %tq qdate
[/CODE]
------------------ copy up to and including the previous line ------------------

Listed 100 out of 4354378 observations


I want to reshape long to wide but it giving error :

values of variable fund_id not unique within permno
Your data are currently long. You are performing a reshape wide. You specified i(permno) and j(fund_id). There are observations within i(permno) with
the same value of j(fund_id). In the long data, variables i() and j() together must uniquely identify the observations.

long wide
+---------------+ +------------------+
| i j a b | | i a1 a2 b1 b2 |
|---------------| <--- reshape ---> |------------------|
| 1 1 1 2 | | 1 1 3 2 4 |
| 1 2 3 4 | | 2 5 7 6 8 |
| 2 1 5 6 | +------------------+
| 2 2 7 8 |
+---------------+
Type reshape error for a list of the problem variables.

Even I tried to drop duplicates

code : reshape wide delta1, i(permno qdate ) j(fund_id) string

Please help.

[Øyvind Snilsberg]

are you sure you want to reshape the data, fund_id seems to take a high number of unique values... you can reshape after dropping duplicates with respect to permno, qdate and fund_id, but how would you decide what value of delta1 to keep?

[pravin chaudhari]

After long to wide i have to merge data with long by permno qdate.

[William Lisowski]

You can use

merge m:1

to merge the data you show - having multiple observations for each combination of permno and qdate - with the data you describe - apparently having a single observation for each combination of permno and qdate.

* Example generated by -dataex-. For more info, type help dataex
clear
input float qdate double permno str5 fund_id float delta1
160 40539 "f14"    .002620869
160 40539 "f386"    .01007914
160 75049 "f36"   -.001889718
160 75049 "f36"  -.0045131226
160 75049 "f36"  -.0009406946
160 75049 "f36"  .00011288052
160 81285 "f126"     .4134931
160 81285 "f152"    .07659303
160 81285 "f207"   -.05502164
160 86238 "f33"  -.0001858864
160 86238 "f33"  .00009136245
end
format %tq qdate
save ~/Downloads/fund, replace

* Example generated by -dataex-. For more info, type help dataex
clear
input float qdate double permno float newvar
160 40539 1
160 75049 2
160 81285 3
160 86238 4
end
format %tq qdate
save ~/Downloads/permno, replace

clear
use ~/Downloads/fund
merge m:1 permno qdate using ~/Downloads/permno
list, clean

. use ~/Downloads/fund

. merge m:1 permno qdate using ~/Downloads/permno

    Result                      Number of obs
    -----------------------------------------
    Not matched                             0
    Matched                                11  (_merge==3)
    -----------------------------------------

. list, clean

        qdate   permno   fund_id      delta1   newvar        _merge  
  1.   2000q1    40539       f14    .0026209        1   Matched (3)  
  2.   2000q1    40539      f386    .0100791        1   Matched (3)  
  3.   2000q1    75049       f36   -.0018897        2   Matched (3)  
  4.   2000q1    75049       f36   -.0045131        2   Matched (3)  
  5.   2000q1    75049       f36   -.0009407        2   Matched (3)  
  6.   2000q1    75049       f36    .0001129        2   Matched (3)  
  7.   2000q1    81285      f126    .4134931        3   Matched (3)  
  8.   2000q1    81285      f152     .076593        3   Matched (3)  
  9.   2000q1    81285      f207   -.0550216        3   Matched (3)  
 10.   2000q1    86238       f33   -.0001859        4   Matched (3)  
 11.   2000q1    86238       f33    .0000914        4   Matched (3)