Creating deciles and taking mean of that

raqthesolid — Tue, 28 Mar 2017 15:49:21 GMT

I am using SAS and trying to make descriptive statistics. I started using it few months back and can do simple analysis but this is tough for me. I am sure someone can help me with this. Thanks in advance. THe variables i have are following; Firm, Year, Month, VDecile, A, B, C, Each month all the Firms are divided into decile portfolio based on VDecile variables at time t=month. IN each decile based on Vdecile(variable name) i want to compute cross-sectional median of A,B,C at t+1 (month+1). Next i want to compute time series mean of these median portfolios.

Thank you very much

Re: Creating deciles and taking mean of that

ballardw — Tue, 28 Mar 2017 16:06:00 GMT

Example input data and the expected result will get you better answers. Replace anything sensitive with dummy values as appropriate, like names of XXX, YYY and ZZZ. Best is to post as datastep code so that we have data to manipulate. A SAS dataset can be turned into datastep code following Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712 will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.

Re: Creating deciles and taking mean of that

raqthesolid — Wed, 29 Mar 2017 18:55:55 GMT

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.

topic Creating deciles and taking mean of that in Statistical Procedures

Creating deciles and taking mean of that

Re: Creating deciles and taking mean of that

Re: Creating deciles and taking mean of that