topic Re: How to populate missing observations with the next available observation? in SAS Programming

How to populate missing observations with the next available observation?

lb16fa — Sun, 24 Jan 2021 23:57:33 GMT

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!

Re: How to populate missing observations with the next available observation?

novinosrin — Mon, 25 Jan 2021 00:12:49 GMT

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;

Re: How to populate missing observations with the next available observation?

Kurt_Bremser — Mon, 25 Jan 2021 00:47:09 GMT

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

Re: How to populate missing observations with the next available observation?

lb16fa — Mon, 25 Jan 2021 04:18:24 GMT

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;

Re: How to populate missing observations with the next available observation?

lb16fa — Mon, 25 Jan 2021 04:23:27 GMT

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;

Re: How to populate missing observations with the next available observation?

Aku — Mon, 25 Jan 2021 08:10:08 GMT

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;

Re: How to populate missing observations with the next available observation?

Kurt_Bremser — Mon, 25 Jan 2021 12:25:45 GMT

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?

Re: How to populate missing observations with the next available observation?

Ksharp — Mon, 25 Jan 2021 11:57:55 GMT

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

Re: How to populate missing observations with the next available observation?

lb16fa — Mon, 25 Jan 2021 22:59:28 GMT

This works for me! Thanks so much.