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

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
Kurt_Bremser
Super User

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;
  

View solution in original post

8 REPLIES 8
novinosrin
Tourmaline | Level 20

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
Quartz | Level 8
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
Super User

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
Quartz | Level 8

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

 

Thank you again

Ksharp
Super User
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
PROC Star

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

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

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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