How to merge quarterly data with annual data based on the most recent fiscal year end observations?

[Jaaa]

Hi SAS experts, 

I want to merge a quarterly dataset with an annual dataset based on the most recent fiscal year end observations so that we keep the observations of the quarterly dataset for which is most recent historical observations next to fiscal year-end.  For example, for fiscal year end (fym) = 1986m5, I would like to merge with quarterly date (ym) in 1986m3 (1986q1). 

This is quarterly dataset:

quarter fyear permno gvkey tmi1 ym wtio fym
2010q3 2010 10001 012994 0 2010m9 .000204595 2010m12
2010q4 2010 10001 012994 0 2010m12 .0001374978 2010m12
2011q1 2011 10001 012994 0 2011m3 .0001510747 2011m12
2011q2 2011 10001 012994 0 2011m6 .0001362973 2011m12
2011q3 2011 10001 012994 0 2011m9 .0000220984 2011m12
2011q4 2011 10001 012994 0 2011m12 .0000223137 2011m12
2012q1 2012 10001 012994 0 2012m3 .0000277935 2012m12
2012q2 2012 10001 012994 0 2012m6 .0000251016 2012m12
2012q3 2012 10001 012994 0 2012m9 .0000199591 2012m12
2012q4 2012 10001 012994 0 2012m12 .0000188157 2012m12
2013q1 2013 10001 012994 0 2013m3 .0000181238 2013m12
2013q2 2013 10001 012994 0 2013m6 .0000653531 2013m12
2013q3 2013 10001 012994 0 2013m9 .0013554013 2013m12
2013q4 2013 10001 012994 0 2013m12 .0000623073 2013m12
2014q1 2014 10001 012994 0 2014m3 .0001164507 2014m12
2014q2 2014 10001 012994 0 2014m6 .000086806 2014m12
2014q3 2014 10001 012994 0 2014m9 .0000988022 2014m12
2014q4 2014 10001 012994 0 2014m12 .0000465412 2014m12
2015q1 2015 10001 012994 0 2015m3 .0000351767 2015m12
2015q2 2015 10001 012994 0 2015m6 .0000423412 2015m12
2015q3 2015 10001 012994 0 2015m9 .000050994 2015m12
2015q4 2015 10001 012994 0 2015m12 .0000425805 2015m12
2002q2 2002 10002 019049 0 2002m6 .0000220374 2002m12
2002q3 2002 10002 019049 0 2002m9 .0000238107 2002m12
2002q4 2002 10002 019049 0 2002m12 .000020816 2002m12

This is annual dataset:

fyear permno year ym gvkey inef und_inv over_inv fym
1988 10001 1988 1988m6 012994 .0729066 .0729066 1988m6
1989 10001 1989 1989m6 012994 .2361533 .2361533 1989m6
1990 10001 1990 1990m6 012994 .1292808 .1292808 1990m6
1991 10001 1991 1991m6 012994 .0317928 .0317928 1991m6
1992 10001 1992 1992m6 012994 .0686157 .0686157 1992m6
1993 10001 1993 1993m6 012994 .0009931 .0009931 1993m6
1994 10001 1994 1994m6 012994 .0380168 .0380168 1994m6
1995 10001 1995 1995m6 012994 .0421782 .0421782 1995m6
1996 10001 1996 1996m6 012994 .0083606 .0083606 1996m6
1997 10001 1997 1997m6 012994 .0310902 .0310902 1997m6
1998 10001 1998 1998m6 012994 .0412929 .0412929 1998m6
1999 10001 1999 1999m6 012994 .0115833 .0115833 1999m6
2000 10001 2000 2000m6 012994 .0003772 .0003772 2000m6
2001 10001 2001 2001m6 012994 .026876 .026876 2001m6
2002 10001 2002 2002m6 012994 .0113567 .0113567 2002m6
2003 10001 2003 2003m6 012994 .0240023 .0240023 2003m6
2004 10001 2004 2004m6 012994 .0543619 .0543619 2004m6
2005 10001 2005 2005m6 012994 .0231107 .0231107 2005m6
2006 10001 2006 2006m6 012994 .0414165 .0414165 2006m6
2007 10001 2007 2007m6 012994 .0393314 .0393314 2007m6
2008 10001 2008 2008m12 012994 .0501416 .0501416 2008m12
2010 10001 2010 2010m12 012994 .0448879 .0448879 2010m12
2011 10001 2011 2011m12 012994 .0593354 .0593354 2011m12
2012 10001 2012 2012m12 012994 .0014087 .0014087 2012m12
2013 10001 2013 2013m12 012994 .0088446 .0088446 2013m12
2014 10001 2014 2014m12 012994 .0061508 .0061508 2014m12
2015 10001 2015 2015m12 012994 .0809812 .0809812 2015m12

Can anyone help me with this issue? Many thanks for your help in advance! 🙂 

[PaigeMiller]

Seems like you would want a many-to-one merge, where you merge on the year number. For this to work, you'd have to change the year number in each data set to have the same variable name. You will need to sort both data sets by year first.

data want;
    merge quarterly(rename=(fyear=year)) annual;
    by year;
run;

[Jaaa]

Hi,

Thank you for your quick reply! The quarterly dataset also has a year variable which was not included. Fyear is fiscal year variable existed in both datasets. 

I am trying to merge the quarterly dataset with the annual dataset based on the fiscal-year-end (fym) observations so we can match the annual observations (und_inv) with the most recent historical quarterly observations (wtio) based on every fiscal-year-end.

Do you possibly know how to do that? Many thanks for your time! 

[PaigeMiller]

Show us a small example of what the output data set will look like.

[PeterClemmensen]

Can you show us what your desired result looks like?

Perhaps this can help you

http://support.sas.com/documentation/cdl/en/etsug/63348/HTML/default/viewer.htm#etsug_expand_sect031...

[mkeintz]

You want to output a dataset with one record per quarter, with all the QTR variables and the ANN variables for the same fiscal year.

 This code does that, assuming each dataset is sorted by GVKEY/FYEAR.

data want;
  set ann (keep=gvkey fyear  in=inq)
      qtr (keep=gvkey fyear  in=ina) ;
  by gvkey fyear;
  if ina then set ann;
  if inq then set qtr;
  if inq then output;
run;

Note that if there is a missing annual record, then the following QTR record will inherit the PREVIOUS annual data.  If you want to prevent that, then insert this statement:

  if last.fyear then call missing(of _all_);

immediately after the "if inq then output" statement.