Quartz | Level 8

## Creating deciles and taking mean of that

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

2 REPLIES 2
Super User

## Re: Creating deciles and taking mean of that

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-dat... 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.

Quartz | Level 8

## Re: Creating deciles and taking mean of that

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.

Discussion stats
• 2 replies
• 1485 views
• 1 like
• 2 in conversation