Solved
Contributor
Posts: 44

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

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

Accepted Solutions
Solution
‎01-24-2018 09:25 AM
Super User
Posts: 10,238

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

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;
``````
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code

All Replies
PROC Star
Posts: 1,799

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

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

4. A logic for conversion/manipulation/transformation

Thank you

Contributor
Posts: 44

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

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.
Solution
‎01-24-2018 09:25 AM
Super User
Posts: 10,238

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

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;
``````
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Contributor
Posts: 44

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

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

Thank you again

PROC Star
Posts: 1,283

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

[ Edited ]

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

Combining Monthly and Quarterly Data

Super User
Posts: 10,778

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

``````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;``````
Posts: 1,337

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

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.

Contributor
Posts: 44

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

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

☑ This topic is solved.