How to merge fiscal year with month(s) of next year

[yotsuba88]

Hi everyone,

I want to merge yearly data (end of fiscal year) and monthly data (next year), could you please help me?

For example. 

I have yearly data (ends of fiscal year)_ JUNE OF YEAR t

June 2000

June 2001

June 2002

I want to merge with next year monthly data, so I will have (from JULY of year t to MAY of year t+1)

June 2000     July 2000

June 2000     Aug 2000

June 2000     Sep 2000

...

June 2000     Jan 2001

June 2000     Feb 2001

June 2000     Mar 2001

June 2000     Apr 2001

June 2000    May 2001

June 2000    June 2001

June 2001    July 2001

Thank you very much.

Ha

[novinosrin]

Your questions seems to lack a few important points

1. How many datasets do you have?

2. Give us a sample/samples clearly as a dataset/table

3. Your wanted output 

4. A logic for conversion/manipulation/transformation

Thank you

[yotsuba88]

Thank you for your feedback and sorry for my short question.
I have 2 datasets and other guys solve my question very well. Anw, I will give more details for next time. Thanks.

[Kurt_Bremser · Accepted solution]

With some made-up data, here a suggestion:

data end_fiscal_year;
input fiscalyear value;
cards;
2000 1
2001 2
2002 3
;
run;

data monthly;
do year = 2000 to 2001;
  do month = 1 to 12;
    output;
  end;
end;
run;

data monthly_with_fy;
set monthly;
if month > 6
then fiscalyear = year + 1;
else fiscalyear = year;
run;

proc sql;
create table want as
select
  a.fiscalyear as prev_year,
  a.value,
  b.fiscalyear,
  b.month
from end_fiscal_year a, monthly_with_fy b
where a.fiscalyear + 1 = b.fiscalyear
;
quit;
  

[yotsuba88]

Thank you so much, it seems work well in my data. I need to double check before make as a solution. 

Thank you again

[PeterClemmensen]

If you have SAS/ETS, this may be of help

Combining Monthly and Quarterly Data

 

[Ksharp]

data have;
input date anydtdte32.;
format date date9.;
cards;
Jun2000
Jun2001
Jun2002
;
run;
data want;
 set have;
 do i=1 to 12;
  _date=intnx('month',date,i);output;
 end;
 drop i;
 format _date date9.;
run;

[mkeintz]

You want to carry forward fiscal year end data to the monthly  records of the following fiscal year.  Assuming you have datasets YEAR and MONTH both sorted by id/date, then this simple program does what you want:

data want (drop=_:);
  set month (in=inm)
      year  (in=iny keep=id date);
  by id date;

  retain _sentinel1 .;
  if iny then set year (rename=(date=fyear_date));
  retain _sentinel2 .;
  else if first.id then call missing (of _sentinel1--_sentinel2);

  if inm;
run;

Note that if you have a missing fiscal year end record then the annual data for the prior fiscal year will propagate to 24 months.

[yotsuba88]

Thank you so much. I am trying to test it. Thank you.