gvkey fyear TA Sum (want) 1700 1995 3311.662 6691.929 1700 1995 1599.234 6691.929 1700 1995 656.226 6691.929 1700 1995 564.76 6691.929 1700 1995 560.047 6691.929 1700 1996 4182.832 10756.85 1700 1996 2068.554 10756.85 1700 1996 1727.069 10756.85 1700 1996 1696.431 10756.85 1700 1996 1081.963 10756.85 1700 1996 841.204 10756.85 1700 1996 577.841 10756.85 1700 1996 . 10756.85 84578 1995 3700.925 . 84578 1995 1312.852 . 84578 1996 1131.176 . 84578 1996 1099.786 . 84578 1996 1067.566 . 84578 1996 848.657 . Here, I want to add TA for the first 5 highest TA. if TA for each year is less than 5 observations, I want them to be shown as missing sum as in for gvkey 84578.  Can anyone help me?  ... View more

GVKEY fyear EXECID AGE 002538 2007 02158 56 002538 2008 02158   002538 2009 02158   002538 2010 02158   002538 2011 42441 57 002538 2012 42441 58 002538 2013 42441 59 002538 2014 42439 63 002538 2015 30776 50 002538 2016 30776 51 002554 1993 00222   002554 1994 00222 59 002554 1995 00222 60 002554 1996 00222   002554 1997 00222 62 002554 1998 00222     I want the following result. It is filling the missing age by execid (based on previously given age) GVKEY fyear EXECID AGE 002538 2007 02158 56 002538 2008 02158 57 002538 2009 02158 58 002538 2010 02158 59 002538 2011 42441 57 002538 2012 42441 58 002538 2013 42441 59 002538 2014 42439 63 002538 2015 30776 50 002538 2016 30776 51 002554 1993 00222   002554 1994 00222 59 002554 1995 00222 60 002554 1996 00222 61 002554 1997 00222 62 002554 1998 00222 63 ... View more

GVKEY fyear EXECID TITLEANN 002538 2007 02158 president & CEO 002538 2008 02158 president & CEO 002538 2009 02158   002538 2010 02158   002538 2011 42441   002538 2012 42441 chmn. & CEO 002538 2013 42441 chmn. & CEO   I want the following result GVKEY fyear EXECID TITLEANN 002538 2007 02158 president & CEO 002538 2008 02158 president & CEO 002538 2009 02158 president & CEO 002538 2010 02158 president & CEO 002538 2011 42441   002538 2012 42441 chmn. & CEO 002538 2013 42441 chmn. & CEO can anyone help me with this?  ... View more

Given Data GVKEY fyear EXECID TITLEANN AGE 002538 2007 02158 president & CEO 56 002538 2008 02158 president & CEO 57 002538 2009 02158   58 002538 2010 02158   59 002538 2011 42441   57 002538 2012 42441   58 002538 2013 42441   59 002538 2014 42439 Chief Financial Officer and Executive Vice President 63 002538 2015 30776   50 002538 2016 30776   51 002554 1993 00222 chmn. & CEO   002554 1994 00222 chmn. & CEO 59 002554 1995 00222 chmn. & CEO 60 002554 1996 00222 chmn. & CEO   002554 1997 00222 chmn. & CEO 62 002554 1998 00222 chmn. & CEO   002555 1992 05687     002555 1993 05687 chmn. & CEO 62 002555 1994 05687 chmn. & CEO 63 002555 1995 05687 chmn. & CEO 64 002555 1996 05687 chmn. & CEO 65 002555 1997 05687 chmn. & CEO 66 002555 1998 05687 chmn. & CEO 67 002555 1999 05687 chmn. & CEO 68 002555 2000 05687 chairman & CEO 69 002555 2001 23867 chairman & CEO 55 002555 2002 23867 chairman, president & CEO 56 002555 2003 23867 chairman, president & CEO   002555 2005 28398 chairman 37 002555 2006 30037 former CEO   002555 2007 30038 CEO 47 002555 2008 30038 president & CEO 48 002555 2009 30038   49 002555 2010 30038   50 002555 2011 30038   51 002562 1992 00263 chmn., pres. & CEO   002562 1993 00263 chmn., pres. & CEO 54 002562 1994 00263 chmn., pres. & CEO 55 I want the following results: So, I want to fill the Titleann and age column. Some of the missing observations will be filled based on previously given information(yellow colored one) and some of the observations will be filled based on the later information(green colored one). And It is not possible to fill some of the missing values as there is no previous and after values are given for those. Missing values will be filled based on "EXECID" Also, GVKEY and FYEAR are important because the same executive may serve in different companies in different year. Can anyone please help me by giving the code? ... View more

Hi, I have some missing datadate. How to fill in the missing date Gvkey  fyear  datadate 1001    2007   20071231 1001    2008    ..... 1001    2009    20091231 1002    2005   20060131 1002    2006    ..... 1002    2007    20080131   data want; update have(obs=0) have; by gvkey fyear; output; run;   The above code doesn't work   I want the following result Gvkey  fyear  datadate 1001    2007   20071231 1001    2008    20081231..... 1001    2009    20091231 1002    2005   20060131 1002    2006    .20070131.... 1002    2007    20080131   So, It is basically the year will change, not the month and day. Datadate is a numeric value ... View more

%let year1=1950; %let year2=2020; proc sql; create table comp as select * from mydat.abc (keep=gvkey year  IND--BLOCK) where fyear between &year1 and &year2 order by gvkey, year; quit; RUN;   My above code works nicely. But I want to make slight changes like the following but it doesn't give me all the variables from IND to Block %let year1=1950; %let year2=2020; proc sql; create table comp as select gvkey, year, IND--BLOCK from mydat.abc  where fyear between &year1 and &year2 order by gvkey, year; quit; RUN; Can anyone tell me how should I use IND--BLOCK and get all the variables between IND and BLOCK following the 2nd example?  ... View more

I am using the following code to left join proc sql; create table want as select * from final3 left join gov_proxy on final3.gvkey=gov_proxy.gvkey and final3.fyear=gov_proxy.fyear; quit; I have 63,654 observations in my final3 dataset and  I have 186679 observations in my gov-proxy dataset  I want to have 63,654 dataset but I am getting 63888 dataset after deleting the duprecs after merging.  I have no duprecs in any one of the dataset before merging.  Can anyone help me with this?  ... View more

gvkey fyear ind sale Growth 1004 1980 1 214 . 1032 1980 1 214 . 1035 1980 1 214 . 1055 1981 1 332 1.551402 1070 1981 1 332 1.551402 1086 1981 1 332 1.551402 1101 1981 1 332 1.551402 1117 1982 1 445 1.340361 1132 1982 1 445 1.340361 1148 1982 1 445 1.340361 1005 1980 2 325 . 1022 1980 2 325 . 1027 1980 2 325 . 1040 1981 2 356 1.095385 1051 1981 2 356 1.095385 1062 1981 2 356 1.095385 1073 1981 2 332 1.095385 1084 1982 2 266 0.801205 1095 1982 2 266 0.801205 1106 1982 2 266 0.801205 I want to calculate industry sales growth. The last column is my expected result. sale/lag(sale). But I want it the way I have shown on the table. Can anyone help?  ... View more