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!
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;
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;
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;
There is no May 31st, 2001 in your source dataset. From where do you take it?
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;
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?
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.