Thank you @ballardw for suggesting datastep. I learned to do that. My data is too big that it will not be feasible to post all here. i am posting a portion of it here.
data work.new; infile datalines dsd truncover; input PERMNO:8. DATE:YYMMDDN8. NCUSIP:$8. PERMCO:8. EXCHCD:2. SICCD:8. PRC:12.5 VOL:10. RET:11.6 RETX:11.6 SHROUT:32. year:32. month:32. TIC:$8. cusipc:$8. GVKEY:$6. DATADATE:YYMMDDN8. CUSIP:$9. SIC:$4. ILLIQUIDITY:32. CR:32.; datalines; 10003,19951229,39031810,7957,3,6020,,1464,,,5043,1995,12,GCBK,39031810,016950,19951231,390318103,6036,1.870138826, 10010,19950831,12709510,7967,3,3840,,8112,,,10359,1995,8,CBOT,12709510,012622,19950831,127095107,3841,0.0890388115, 10012,20050831,23326910,7969,3,3674,,1436,,,23745,2005,8,DPAC,23326910,011907,20050831,233269109,3674,, 10035,20031231,38209110,7983,3,5730,,7293,,,27268,2003,12,GGUY,38209110,011974,20031231,382091106,5731,0.0591831131, 10039,20010629,00208K10,7987,3,7372,,6611,,,5469,2001,6,ATCM,00208K10,011948,20010630,00208K105,3576,, 10048,19991029,15650310,7996,3,4840,,1049,,,33701,1999,10,CTYA,15650310,012102,19991031,156503104,4841,,12 10056,20061031,65163310,8005,3,6036,,156,,,4075,2006,10,NMIL,65163310,017081,20061031,651633109,6036,, 10059,19940429,68486520,8006,3,1040,,669,,,5194,1994,4,OFSLY,68486520,102519,19940430,684865207,6799,0.6061951881, 10060,19980630,35614220,8007,3,1040,,13013,,,26961,1998,6,FSCNY,35614220,018461,19980630,356142208,1040,0.1248946616, 10064,19970331,90212810,8010,1,3944,,52268,,,34827,1997,3,TTI,90212810,021237,19970331,88162F105,1389,0.0050546197, 10071,19970930,01390210,8015,1,6211,,,,,,1997,9,AB,01390210,014592,19970930,01881G106,6799,, 10074,19960329,00078110,5271,3,3460,,5618,,,5052,1996,3,ABSI,00078110,001009,19960331,000781104,3460,, 10075,19990730,91843510,8018,3,7330,,12913,,,28963,1999,7,VWRX,91843510,012239,19990731,918435108,5040,0.0131786512, 10080,20031128,61576210,8024,3,5072,,,,,,2003,11,MHCO,61576210,011942,20031130,615762101,5072,, 10082,19950929,00087230,5793,3,4840,,15808,,,10425,1995,9,ACSE,00087230,001011,19950930,000872309,4841,0.2686747898, 10087,20001031,07465510,8031,3,5960,,944,,,7231,2000,10,BUTI,07465510,013119,20001031,074655101,2844,0.774369218, 10094,19971231,89903510,8037,3,5710,,7624,,,6600,1997,12,TUES,89903510,011944,19971231,899035505,5331,0.0093309989,14 10095,19941230,87159410,8038,3,2830,,62032,,,25922,1994,12,SYGN,87159410,011949,19941231,871594107,2834,0.0040579974, 10106,19950131,53473510,8047,3,6020,,41,,,900,1995,1,LNSB,53473510,016918,19950131,534735105,6036,, 10114,20040831,00432520,9831,3,7372,,699129,,,129574,2004,8,AKLM,00432520,014524,20040831,004325205,7372,, 10119,20070928,90130N10,8064,1,6331,,31746,,,88126,2007,9,TW,90130N10,029887,20070930,891894107,8742,0.0003413723, 10120,19980529,72408110,8065,1,6211,,491,,,19129,1998,5,PJC,72408110,008605,19980531,724078100,6211,, 10122,20021031,58413110,8067,3,6030,,1584,,,7802,2002,10,MDBK,58413110,017140,20021031,584131106,6036,0.0019692838, 10123,20020628,00452710,9754,1,7312,,5941,,,24298,2002,6,AK,00452710,001095,20020630,004527107,4833,0.022940774, 10126,20000731,68241910,8072,1,6022,,1355,,,33678,2000,7,OV,68241910,013055,20000731,682419106,6020,, 10128,20010228,25469V10,8075,3,3560,,12236,,,8562,2001,2,DTEK,25469V10,013410,20010228,25469V102,3990,, 10141,19960731,36471910,8090,3,5960,,5315,,,3255,1996,7,GNDR,36471910,012452,19960731,364719104,5961,4.34915996, 10154,19960229,00103010,81,3,3662,,994,,,3660,1996,2,AELNA,00103010,001017,19960229,001030105,3812,0.0501176265, 10155,20010629,28485330,8079,3,3820,,985,,,6200,2001,6,ELGT,28485330,012094,20010630,284853306,3310,, 10179,19971031,74937110,8122,3,6036,,3659,,,14693,1997,10,RCSB,74937110,016238,19971031,749371100,6036,, 10185,19991130,59017510,8128,3,2750,,13994,,,15935,1999,11,MRLL,59017510,012380,19991130,590175105,2750,0.0043732373,13 10212,19960628,45683430,7278,3,3840,,17610,,,10554,1996,6,IFRA,45683430,011750,19960630,456834308,3842,0.0384587613, 10213,19961231,03029010,8146,3,6310,,52693,,,24063,1996,12,ATVC,03029010,016661,19961231,030290100,6321,0.0007715001,11 10231,19970930,20363010,8157,3,6710,,,,,,1997,9,CBNH,20363010,016792,19970930,203630108,6036,, 10242,19980831,89323N10,8168,3,6710,,25366,,,11739,1998,8,TRFI,89323N10,017241,19980831,89323N102,6020,0.0050370307, 10248,19960229,17184210,8174,3,3070,,19,,,2965,1996,2,CIMC,17184210,012204,19960229,171842107,3080,, 10254,19870831,85935910,8180,3,5940,,24,,,4943,1987,8,STRL,85935910,005559,19870831,859241101,1600,, 10281,19980731,05277710,8204,3,7370,,2874,,,7997,1998,7,AUTO,05277710,012210,19980731,052777109,4210,, 10285,19920131,00142310,5148,2,1731,,3739,,,6735,1992,1,AIM,00142310,001173,19920131,008015307,3812,, 10287,19920630,00207510,8209,2,7394,,,,,,1992,6,ATI,00207510,010405,19920630,01741R102,3350,,8 10307,20001130,60040410,8226,3,3440,,,,,,2000,11,MBSI,60040410,012214,20001130,600404107,3272,, 10309,19990730,83050610,8229,3,6030,,0,,,1452,1999,7,SKAN,83050610,021420,19990731,830506101,6036,, 10319,19940831,32054T10,8240,3,6020,,98,,,2327,1994,8,FIBI,32054T10,017043,19940831,32054T104,6035,, 10328,19970630,83444710,8248,3,2830,,391,,,24497,1997,6,SOMA,83444710,012232,19970630,834447104,2836,, 10337,19960930,69670510,8260,3,6020,,0,,,1131,1996,9,PSSB,69670510,019308,19960930,696705102,6035,, 10341,20040430,00350P10,8264,3,6030,,3186,,,4032,2004,4,ABBK,00350P10,016605,20040430,00350P100,6036,0.0363649602, 10352,19960329,56763410,8276,3,2840,,1105,,,3596,1996,3,MRTA,56763410,012350,19960331,567634100,7389,,
;;;;
Hopefully this will help to understand the nature of variables.
**What I am trying to do is these things;
1) Creating decile (dividing data into 10 groups) at time 't' every month (by month i mean year and month combination) t=month.
2) Produce median of each decile at time t+1 for variables PRC, VOL. ILLIQUIDITY.
3) Compute the mean in each decile group.
Thank you very much.
... View more
