Data reshape wide to long

Hello,

I have run into a data reshape problem, Table 1 provides the information about the dataset. Each row provides a daily observation of a unique firm relative to an event date [-150, 20], the dates are in the first row.

The dates in rows are in chronological sequences. For each, unique firm if the date does not fall in the range of event window it is marked as empty but if it is the range but there is no observation than it is marked as zero.

I used the followin code to reshape data

Code:

reshape long ri, i(index) j(day) string

There is a problem when I reshape the data the results are entirely out of sync. Refer to table two, which provides the transformation of the first A with event date 6/10/2004 row. Ideally, it should have had daily data starting 1/12/2004 to 06/30/2004 and blanks for all remaining dates but the day variable show completely different results.

Code:

* TABLE 1
Example generated by -dataex-. To install: ssc install dataex
clear
input float index str13 CUSIP_YEAR int MeetingDate str3(ri17May05 ri18May05 ri19May05 ri20May05 ri21May05 ri22May05 ri23May05 ri24May05 ri25May05 ri26May05 ri27May05 ri28May05 ri29May05 ri30May05 ri31May05 ri01Jun05)
  1 "00751Y10_2005" 16574 " 24" " 19" " 50" " 21" " 26" "  0" " 14" " 38" " 48" " 25" " 22" " 18" " 39" " 24" " 29" " 53"
  2 "03783310_2005" 16547 " 37" " 36" " 18" " 48" " 23" "  9" " 46" " 32" " 36" " 50" " 51" " 24" " 20" "  0" " 23" ""   
  3 "01310410_2005" 16589 " 58" " 49" " 50" " 48" " 73" " 76" " 73" " 54" " 61" " 39" " 62" " 70" " 69" " 65" " 56" " 50"
  4 "00282410_2005" 16548 " 40" " 35" " 39" " 34" " 25" " 37" " 62" " 27" " 48" " 36" " 24" " 20" " 21" " 35" " 52" ""   
  5 "00819010_2005" 16736 " 79" " 81" " 69" " 77" " 39" " 41" " 47" " 65" " 74" " 85" " 59" " 22" " 27" " 48" " 61" " 79"
  6 "00724F10_2005" 16554 " 30" " 31" " 31" " 34" "  0" "  0" " 31" " 45" " 92" " 32" " 36" "  0" "  0" "  0" "  0" ""   
  7 "00088630_2005" 16496 ""    ""    ""    ""    ""    ""    ""    ""    ""    ""    ""    ""    ""    ""    ""    ""   
  8 "03265410_2005" 16503 ""    ""    ""    ""    ""    ""    ""    ""    ""    ""    ""    ""    ""    ""    ""    ""   
  9 "03948310_2005" 16743 ""    ""    ""    ""    ""    ""    ""    ""    ""    ""    ""    ""    ""    ""    ""    " 42"
 10 "02360810_2005" 16552 "  0" " 29" " 29" "  0" "  0" "  0" " 57" " 28" "  0" " 30" " 33" "  0" "  0" "  0" "  0" ""   
 11 "00817Y10_2005" 16555 " 13" "  0" "  0" "  0" " 25" "  0" "  0" " 13" "  0" " 14" " 32" "  0" "  0" " 23" " 14" ""   
 12 "03466310_2005" 16580 " 31" "  0" " 32" " 35" "  0" " 55" "  0" " 31" "  0" " 32" " 37" "  0" "  0" "  0" " 32" "  0"
 13 "31314830_2005" 16603 "  0" " 24" " 25" "  0" "  0" " 43" " 71" "  0" "  0" "  0" " 42" "  0" " 48" "  0" "  0" " 48"
 14 "01165910_2005" 16573 " 37" "  0" " 19" "  0" "  0" "  0" "  0" " 18" " 38" " 58" "  0" " 35" "  0" " 62" " 19" "  0"
 15 "02144110_2005" 16566 "  0" "  0" "  0" "  0" "  0" "  0" "  0" "  0" "  0" "  0" " 45" "  0" "  0" "  0" "  0" ""   
 16 "02153V10_2005" 16757 ""    ""    ""    ""    ""    ""    ""    ""    ""    ""    ""    ""    ""    ""    ""    "  0"
 17 "00790310_2005" 16554 " 52" " 48" " 41" " 50" " 70" " 59" " 40" " 55" " 55" " 59" " 47" " 78" " 77" " 72" " 55" ""   
 18 "03116210_2005" 16567 " 21" "  0" " 22" " 23" "  0" "  0" "  0" "  0" " 22" " 22" "  0" " 41" "  0" "  0" "  0" ""   
 19 "02313510_2005" 16573 " 20" "  0" " 21" " 57" " 37" "  0" " 20" "  0" " 21" " 21" "  0" "  0" " 41" "  0" "  0" " 41"
 20 "03675210_2005" 16566 " 21" " 43" " 89" " 71" "  0" " 51" " 14" " 28" " 29" " 15" "  0" " 27" " 28" "  0" " 14" ""   
 21 "03251110_2005" 16568 " 61" " 63" " 48" " 83" " 46" " 37" " 40" " 78" " 50" " 62" " 43" " 48" " 46" " 38" " 76" " 83"
 22 "03789910_2005" 16568 "  0" "  0" "  0" "  0" "  0" "  0" "  0" "  0" "  0" "  0" "  0" "  0" "  0" "  0" "  0" "  0"
 23 "03979310_2005" 16581 " 57" " 38" " 30" " 28" " 29" " 34" " 83" " 51" " 71" " 66" " 22" " 49" " 26" " 48" "100" " 48"
 24 "04548710_2005" 16553 " 16" " 11" "  0" " 36" "  0" "  0" " 11" "  0" " 11" " 45" " 13" "  0" "  0" "  0" " 11" ""   
 25 "03216510_2005" 16547 " 15" " 47" " 80" " 69" " 85" "  0" " 24" " 15" " 39" " 16" " 18" " 30" " 31" " 79" " 16" ""   
 26 "00922R10_2005" 16561 "  0" "  0" "  0" " 51" "  0" "  0" "  0" "  0" "  0" " 48" "  0" "  0" "  0" "  0" "  0" ""   
 27 "02581610_2005" 16553 "  0" "  0" " 35" "  0" "  0" "  0" "  0" " 22" "  0" " 24" " 27" "  0" "  0" "  0" " 23" ""   
 28 "01736110_2005" 16568 " 80" " 16" " 17" " 36" " 30" "  0" " 16" " 16" " 41" " 34" " 38" " 31" " 32" " 54" " 25" " 24"
 29 "05480210_2005" 16568 " 53" "  0" "  0" "  0" "  0" "  0" "  0" "  0" "  0" " 56" "  0" "  0" "  0" "  0" "  0" "  0"
 30 "09702310_2005" 16558 " 60" " 78" " 57" " 42" " 80" " 63" " 51" " 45" " 65" " 35" " 76" " 48" " 25" " 35" " 57" ""   
 31 "06050510_2005" 16553 " 44" " 54" " 41" " 49" " 57" " 40" " 49" " 87" " 49" " 42" " 37" " 34" " 36" " 60" " 27" ""   
 32 "07181310_2005" 16559 " 74" " 15" " 31" "100" " 27" " 27" " 53" " 59" " 38" " 63" " 61" " 29" " 60" " 25" " 61" ""   
 33 "07589610_2005" 16617 "  0" "  0" " 46" "  0" "  0" "  0" "  0" "  0" " 44" "  0" "  0" "  0" "  0" "  0" "  0" "  0"
 34 "06738310_2005" 16546 "  0" "  0" " 23" "  0" "  0" "  0" "  0" " 44" " 23" "  0" " 26" "  0" " 45" "  0" " 22" ""   
 35 "09179710_2005" 16552 " 22" "  0" "  0" "  0" "  0" "  0" "  0" "  0" "  0" "  0" "  0" "  0" "  0" "  0" "  0" ""   
 36 "07588710_2005" 16468 ""    ""    ""    ""    ""    ""    ""    ""    ""    ""    ""    ""    ""    ""    ""    ""   
 37 "07332510_2005" 16610 "  0" " 28" "  0" " 48" "  0" " 51" " 57" " 28" "  0" " 30" " 34" "  0" "  0" "  0" "  0" " 57"
 38 "05722410_2005" 16554 " 19" " 19" "  0" " 21" " 53" " 34" "  0" " 38" " 39" " 20" "  0" "  0" " 39" " 33" " 39" ""   
 39 "06405710_2005" 16538 " 14" "  9" " 11" " 14" " 22" " 16" " 19" " 24" " 18" " 13" " 23" " 17" " 14" "  6" " 14" ""   
 40 "05548G10_2005" 16610 " 0"  " 0"  " 0"  " 0"  " 0"  " 0"  " 0"  " 0"  " 0"  " 0"  " 0"  " 0"  " 0"  " 0"  " 0"  " 0" 
 41 "05849810_2005" 16553 " 29" "  0" " 61" "  0" "  0" "  0" "  0" "  0" "  0" "  0" "  0" "  0" "  0" "  0" " 29" ""   
 42 "07986010_2005" 16551 " 32" " 27" " 17" " 36" " 20" " 38" " 32" " 31" " 32" " 22" " 25" " 20" " 65" " 63" " 32" ""   
 43 "11012210_2005" 16559 " 42" "  0" "  0" "  0" "  0" "  0" "  0" "  0" "  0" "  0" "  0" "  0" "  0" "  0" " 86" ""   
 44 "05593610_2005" 16602 "  0" "  0" "  0" "  0" "  0" "  0" "  0" "  0" "  0" "  0" "  0" "  0" "  0" "  0" "  0" "  0"
 45 "11132010_2005" 16554 "  0" " 26" "  0" "  0" "  0" "  0" " 52" "  0" " 26" " 27" " 31" "  0" "  0" "  0" " 53" ""   
 46 "06830610_2005" 16743 ""    ""    ""    ""    ""    ""    ""    ""    ""    ""    ""    ""    ""    ""    ""    "  0"
 47 "10113710_2005" 16566 " 29" "  0" "  0" " 33" "  0" "  0" " 30" " 29" " 30" " 31" " 69" "  0" "  0" "  0" "  0" ""   
 48 "10112110_2005" 16567 "  0" " 45" " 47" "  0" "  0" "  0" "  0" "  0" "  0" " 47" "  0" "  0" "  0" " 75" "  0" ""   
 49 "20588710_2005" 16701 "  0" " 23" " 23" " 25" " 42" "  0" " 23" " 22" " 23" " 24" " 27" " 43" " 46" "  0" " 23" " 23"
 50 "14166510_2005" 16576 " 45" " 69" " 24" "  0" " 42" " 81" " 23" " 45" " 58" "  0" " 27" "  0" "  0" "  0" "  0" " 23"
 51 "19416210_2005" 16560 " 78" " 63" " 70" " 69" " 36" " 28" " 64" " 69" " 36" " 53" " 83" " 67" " 71" " 59" " 68" ""   
 52 "17958410_2005" 16615 " 52" " 52" " 39" " 37" " 52" " 34" " 62" " 47" "100" " 54" " 44" " 36" " 38" " 31" " 43" " 62"
 53 "55448910_2005" 16610 " 61" " 27" " 55" " 29" " 33" "  0" " 45" " 87" " 62" " 18" " 62" " 34" " 71" "  0" " 36" " 36"
 54 "20342P10_2005" 16581 "  0" "  0" " 55" "  0" "  0" "  0" "  0" "  0" "  0" "  0" "  0" "  0" "  0" "  0" "  0" "  0"
 55 "20030N10_2005" 16588 "  0" "  0" "  0" "  0" " 76" "  0" "  0" "  0" "  0" "  0" "  0" "  0" "  0" "  0" "  0" " 43"
 56 "23102110_2005" 16566 " 17" " 35" " 18" "  0" " 21" " 41" " 17" " 23" " 17" " 18" " 20" " 22" " 23" " 19" " 18" ""   
 57 "14170510_2005" 16567 " 25" " 26" " 26" " 28" " 46" "  0" " 25" " 25" "  0" " 53" " 30" " 48" " 51" "  0" "  0" ""   
 58 "15189T10_2005" 16589 " 26" "  0" " 27" "  0" "  0" "  0" "  0" "  0" " 26" " 41" " 30" "100" "  0" "  0" " 52" " 26"
 59 "19089710_2005" 16575 " 0"  " 0"  " 0"  " 0"  " 0"  " 0"  " 0"  " 0"  " 0"  " 0"  " 0"  " 0"  " 0"  " 0"  " 0"  " 0" 
 60 "14040H10_2005" 16554 " 44" "  0" " 23" "  0" " 41" " 40" " 22" "  0" "  0" " 23" " 26" "  0" " 45" " 38" " 23" ""   
 61 "17275R10_2005" 16755 ""    ""    ""    ""    ""    ""    ""    ""    ""    ""    ""    ""    ""    ""    ""    " 43"
 62 "12640810_2005" 16560 " 26" " 31" " 32" " 24" " 24" "  0" " 27" " 30" " 54" " 19" " 36" " 59" " 18" " 29" " 36" ""   
 63 "17290810_2005" 16727 "  0" " 44" "  0" " 49" "  0" "  0" " 89" "  0" "  0" "  0" "  0" "  0" "  0" " 74" "  0" "  0"
 64 "21683110_2005" 16559 " 21" " 22" "  0" " 24" "  0" " 38" " 22" "  0" " 22" "  0" " 25" " 41" "  0" " 36" "  0" ""   
 65 "12665010_2005" 16568 " 62" " 77" " 77" " 62" " 47" " 68" " 69" " 67" " 67" " 70" " 72" " 64" " 59" " 56" " 80" " 73"
 66 "16676410_2005" 16553 " 36" " 18" " 28" " 20" "  0" "  0" " 28" "  0" " 18" " 19" " 21" " 35" "  0" " 31" " 19" ""   
 67 "26353410_2005" 16553 " 58" " 71" " 70" " 54" " 47" " 42" " 73" " 65" " 75" " 67" " 64" " 48" " 43" " 49" " 87" ""   
 68 "25406710_2005" 16577 " 83" " 55" " 75" " 65" " 27" " 13" " 69" " 84" " 59" " 79" " 43" " 28" " 37" " 30" " 85" " 77"
 69 "23585110_2005" 16560 " 27" " 93" " 19" " 20" " 33" " 33" " 18" " 45" " 37" " 19" " 54" " 35" " 37" " 30" " 46" ""   
 70 "26056110_2005" 16546 " 73" " 64" " 59" " 64" " 81" " 85" " 59" " 59" " 57" " 72" " 58" " 77" " 97" " 83" " 74" ""   
 71 "26054310_2005" 16568 " 65" " 69" " 39" " 56" " 43" " 20" " 63" " 50" " 60" " 58" " 53" " 35" " 47" " 18" " 68" " 63"
 72 "25066010_2005" 16701 "  0" "  0" "  0" "  0" "  0" "  0" "  0" "  0" "  0" "  0" "  0" "  0" "  0" "  0" "  0" "  0"
 73 "27864210_2005" 16610 " 72" " 70" " 70" " 78" " 87" " 89" " 69" " 76" " 68" " 69" " 77" " 87" " 86" " 80" " 70" " 72"
 74 "27886510_2005" 16562 " 21" " 21" "  0" " 35" "  0" "  0" " 22" " 31" " 43" " 22" "  0" "  0" "  0" " 35" " 43" ""   
 75 "20911510_2005" 16572 " 85" " 88" " 91" " 89" " 84" " 68" " 93" " 93" " 82" " 81" " 99" " 73" " 80" " 68" " 97" " 92"
 76 "28102010_2005" 16575 "  0" "  0" "  0" " 52" "  0" "  0" "  0" "  0" "  0" "  0" "  0" "  0" "  0" "  0" " 47" "  0"
 77 "26864810_2005" 16561 " 64" " 47" " 65" " 63" " 28" " 19" " 66" " 66" " 68" " 60" " 85" " 15" " 31" " 26" " 84" ""   
 78 "29101110_2005" 16468 ""    ""    ""    ""    ""    ""    ""    ""    ""    ""    ""    ""    ""    ""    ""    ""   
 79 "29364G10_2005" 16569 " 21" " 21" " 22" " 24" " 39" "  0" " 21" " 32" " 43" " 44" "  0" "  0" " 42" "  0" "  0" " 21"
 80 "30064510_2005" 16736 " 25" " 25" "  0" " 28" " 45" " 45" " 38" " 49" "  0" " 26" "  0" "  0" " 51" "  0" " 76" " 25"
 81 "30213010_2005" 16560 "  0" " 27" " 28" "  0" "  0" "  0" "  0" " 26" "  0" "  0" "  0" "  0" " 53" "  0" "  0" ""   
 82 "35671D85_2005" 16561 "  0" "  0" " 33" "  0" "  0" " 56" "  0" "  0" " 32" "  0" " 37" "  0" "  0" "  0" "  0" ""   
 83 "31428X10_2005" 16705 "  0" " 29" " 30" " 96" " 52" "  0" " 58" " 28" "  0" "  0" " 34" "  0" "  0" "  0" "  0" "  0"
 84 "33793210_2005" 16573 " 69" " 63" " 61" " 79" " 61" " 80" " 81" " 63" " 69" " 77" " 82" " 66" " 78" " 65" " 60" " 59"
 85 "34341210_2005" 16553 "  0" "  0" "  0" "  0" " 53" "  0" " 29" " 28" " 29" "  0" "  0" "  0" "  0" "  0" "  0" ""   
 86 "32079510_2005" 16559 " 0"  " 0"  " 0"  " 0"  " 0"  " 0"  " 0"  " 0"  " 0"  " 0"  " 0"  " 0"  " 0"  " 0"  " 0"  ""   
 87 "31374720_2005" 16560 "  0" " 69" " 24" "  0" "  0" "  0" "  0" " 68" " 23" "  0" " 27" " 44" "  0" " 38" " 23" ""   
 88 "39164810_2005" 16587 "  0" "  0" "  0" "  0" "  0" "  0" "  0" "  0" "  0" "  0" "  0" "  0" "  0" "  0" "  0" "  0"
 89 "36955010_2005" 16560 " 52" " 28" " 50" " 37" " 26" " 51" " 34" " 33" " 29" " 40" " 45" " 18" " 48" " 48" " 43" ""   
 90 "36960410_2005" 16553 " 80" " 76" " 77" " 74" " 72" " 76" " 76" " 78" " 84" " 74" " 76" " 77" " 75" " 71" " 92" ""   
 91 "37291710_2005" 16582 " 25" " 26" "  0" "  0" "  0" " 46" " 26" " 25" "  0" "  0" " 30" "  0" "  0" "  0" "  0" " 26"
 92 "37555810_2005" 16566 "  0" "  0" "  0" " 25" " 42" "  0" " 23" " 23" " 46" " 24" "  0" " 44" "  0" "  0" "  0" ""   
 93 "37033410_2005" 16705 " 50" " 55" " 70" " 59" " 34" " 35" " 57" " 70" " 71" " 56" " 55" " 28" " 46" " 50" " 73" " 67"
 94 "38255010_2005" 16552 " 65" " 69" " 62" " 64" " 68" " 80" " 54" " 53" " 62" " 56" " 50" " 78" " 75" " 78" " 54" ""   
 95 "36762610_2005" 16575 "  0" " 57" " 29" " 32" "  0" " 50" " 28" "  0" "  0" " 29" " 33" "  0" "  0" "  0" "  0" " 29"
 96 "40382910_2005" 16779 ""    ""    ""    ""    ""    ""    ""    ""    ""    ""    ""    ""    ""    ""    ""    ""   
 97 "40621610_2005" 16574 " 50" " 44" " 69" " 51" " 65" " 34" " 50" " 48" " 46" " 54" " 38" " 45" " 36" " 52" " 54" " 61"
 98 "40414L10_2005" 16568 " 21" " 21" " 22" "  0" " 39" "  0" " 21" " 42" " 21" " 22" "  0" "  0" "  0" "  0" " 32" " 21"
 99 "42193310_2005" 16482 ""    ""    ""    ""    ""    ""    ""    ""    ""    ""    ""    ""    ""    ""    ""    ""   
100 "43851610_2005" 16551 " 47" " 43" " 39" " 63" " 26" " 25" " 43" " 42" " 38" " 54" "100" " 36" " 57" " 24" " 48" ""   
end
format %tdnn/dd/CCYY MeetingDate

BJ Data Tech Solution

Home / Data Cleaning / Data management / Data Processing / Data reshape wide to long
Data reshape wide to long

0 Response to Data reshape wide to long

Post a Comment

Home / Data Cleaning / Data management / Data Processing / Data reshape wide to long Data reshape wide to long

Related Posts with Data reshape wide to long

0 Response to Data reshape wide to long

Post a Comment

Home / Data Cleaning / Data management / Data Processing / Data reshape wide to long
Data reshape wide to long