🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Quartz | Level 8

## 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

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## 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;
``````
8 REPLIES 8
Tourmaline | Level 20

## 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

3. Your wanted output

4. A logic for conversion/manipulation/transformation

Thank you

Quartz | Level 8

## 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.
Super User

## 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;
``````
Quartz | Level 8

## 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

Super User

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

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

Combining Monthly and Quarterly Data

Super User

## 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;``````
Jade | Level 19

## 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.

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
Quartz | Level 8

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

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

Discussion stats
• 8 replies
• 1460 views
• 3 likes
• 6 in conversation