How to populate missing observations with the next available observation?

[lb16fa]

Hi everyone!

I have a dataset where I need to populate each observation with which fiscal year they belong to. Each company has the fiscal year end populated as below, but I need every observation to be matched with its appropriate year-end. Essentially, if it's empty, I want it to capture the next available observation. Is there any way I could do this?

Example current data:

Ticker                Date                  Fiscal Year End

APPL                01/31/2000                 .

APPL                02/28/2000                 . 

APPL                03/31/2000                 .

APPL                04/30/2000                 .

APPL                05/31/2000          05/31/2000

APPL                06/30/2000

Example wanted result:

Ticker                Date                  Fiscal Year End

APPL                01/31/2000         05/31/2000

APPL                02/28/2000         05/31/2000

APPL                03/31/2000         05/31/2000

APPL                04/30/2000         05/31/2000

APPL                05/31/2000         05/31/2000

APPL                06/30/2000         05/31/2001

Thanks in advance!

[novinosrin]

Hi @lb16fa  Is your data consistent and a good representative of your real. Can I assume you just want to populate the missing values with the non missing value -

proc sql;

 create table want as

 select ticker,date, max(fiscal_year_end) as fiscal_year_end

 from have

group by ticker

order by ticker, date;

quit;

[lb16fa]

Hi @novinosrin thanks for the quick response! This method isn't working for me because I have multiple fiscal years of data and multiple tickers, I received an output with a table with the same fiscal year end date for each one. I found the code below. It seems to be working for me with just one issue, I can't group the variables by ticker. I'd like the code to check if the ticker is the same before populating it with the next fiscal year end date (as if it were grouped using a by statement) Any ideas on a new code or updates to this one? Thank you so much!

data want;
do until(fiscal_year_end or last);
  set have end=last;
  if not missing(fiscal_year_end) then want_value=fiscal_year_end;
 end;
 do until(fiscal_year_end or _last);
  set have end=_last;
  output;
 end;
run;

[Kurt_Bremser]

There is no May 31st, 2001 in your source dataset. From where do you take it?

[lb16fa]

Hi @Kurt_Bremser thank you also for the quick response! The May 31st, 2001 would come from the next fiscal year end date below it. Every date observation is empty with the exception of the one that aligns with the true fiscal year end. I mentioned this is the previous reply, but I found this code that works for me with the exception that I'm unable to group by ticker (so in several cases the newly populated "fiscal year end" is associated with a different ticker). Is there any way for this code to check that the ticker is the same, or to group by ticker? Thanks again!

data want;
do until(fiscal_year_end or last);
  set have end=last;
  if not missing(fiscal_year_end) then want_value=fiscal_year_end;
 end;
 do until(fiscal_year_end or _last);
  set have end=_last;
  output;
 end;
run;

[Kurt_Bremser]

This illustrates my previous question nicely. When there is no observation containing the end date for the final fiscal year of a ticker, how do you get it?

[Aku · Accepted solution]

With Proc SQL it's quite easy, just pick have -table twice:

proc sql;
create table want as
select a.Ticker,
a.Date,
b.Fiscal_Year_End
from Have A
left join have b
on A.Ticker = B.Ticker
and B.Date ge A.Date
and B.Fiscal_Year_End gt .
group by A.Ticker, A.Date
having B.Fiscal_Year_End = min(B.Fiscal_Year_End);
quit;

[lb16fa]

This works for me! Thanks so much.

[Ksharp]

data want;
merge have have(keep=ticker fiscal_year_end where=(fiscal_year_end is not missing));
by ticker;
run;