Help using Base SAS procedures

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

Accepted Solution Solved
Reply
Contributor
Posts: 44
Accepted Solution

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

Posted in reply to yotsuba88

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

View solution in original post


All Replies
PROC Star
Posts: 1,799

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

Posted in reply to yotsuba88

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

Contributor
Posts: 44

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

Posted in reply to novinosrin
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

Posted in reply to yotsuba88

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

Posted in reply to KurtBremser

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 ]
Posted in reply to yotsuba88

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

Posted in reply to yotsuba88
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;
Trusted Advisor
Posts: 1,337

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

Posted in reply to yotsuba88

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.

Need further help from the community? Please ask a new question.

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