BookmarkSubscribeRSS Feed
coba
Calcite | Level 5
I searched in the forum but couldn't find the answer I am looking for.

I have a data set containing information per quarter, for example, (format date is date7.), How do I create FY?

type date qtr FY

A 01oct08 4 2008/2009
A 01jan09 1 2008/2009
A 01apr09 2 2008/2009
A 01jul09 3 2008/2009
A 01oct09 1 2009/2010
more lines .... and more columns

thank you very much for your help just to clarify I have not been able to create the field "FY" showing in the example, I would like to be able to group all my dates in this way


Message was edited by: coba
12 REPLIES 12
Paige
Quartz | Level 8
IF MONTH(DATE)>=10 then FY=YEAR(DATE)+1;
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
Better to consider setting up a SAS PROC FORMAT using instream data or with a SAS input file (using CNTLIN= parameter) and define the calendar start-date of each fiscal period along with the display information that represents that fiscal period. Here is an example:

PROC FORMAT;
VALUE FISCYRPD
'01OCT2008'D = '1 2008/2009'
'01NOV2008'D = '2 2008/2009'
'01DEC2008'D = '3 2008/2009'
OTHER = '?';
RUN;

If needed (likely so), the "formatted" character-string field above can be parsed into two separate variables for sorting/display purposes.

Or if the period always start on the 1st date, use INTNX to increment the date value up/back to the appropriate fiscal period-start-date and use a suitable output display FORMAT such as MMYY7. to display the resulting fiscal period-year.

Scott Barry
SBBWorks, Inc.
coba
Calcite | Level 5
The only thing I look against is that it would become a long list... as I have more than 25 years data per quarter!
Thank you very much for the display format.
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
You may be able to come up with a SAS derivation algorithm to generate the "year/month" start-date for each fiscal period, otherwise, yes, you are face with defining each period and then start-date to end-date range. Unless the "offset" is always some number of months forward/backward with a 1st of the month start-date, which can be derived programatically.

Scott Barry
SBBWorks, Inc.
coba
Calcite | Level 5
this is my test data:

type date
A 01-Oct-09
A 01-Jan-10
A 01-Apr-10
A 01-Jul-10
A 01-Oct-10
A 01-Jan-11
A 01-Apr-11
A 01-Jul-11

this is the test program

data work.fy1 (KEEP=TYPE DATE FY);
set work.fy;
format FY crpyrf.;
YEAR=YEAR(DATE) ;
QTR=QTR(DATE) ;
MTH=MONTH(DATE);
if qtr=4 then FY=year; else FY=year-1 ;
if qtr=4 then qtr=1; else qtr=qtr+1;
RUN;

this is the results:
type date FY
A 01-Oct-09 09-Oct
A 01-Jan-10 09-Oct
A 01-Apr-10 09-Oct
A 01-Jul-10 09-Oct
A 01-Oct-10 2010
A 01-Jan-11 2010
A 01-Apr-11 2010
A 01-Jul-11 2010

how do I manage to get this:
type date FY
A 01-Oct-09 09/10
A 01-Jan-10 09/10
A 01-Apr-10 09/10
A 01-Jul-10 09/10
A 01-Oct-10 10/11
A 01-Jan-11 10/11
A 01-Apr-11 10/11
A 01-Jul-11 10/11

thank you very much for your help
Patrick
Opal | Level 21
Hi
Nothing that Scott didn't suggest already - only a fully working code example.
HTH
Patrick

data ctrl;
start='01oct1990'd;
retain fmtname 'FISCYRPD' type 'n';
do while (start lt '01oct2050'd);
end=intnx('month3',start,1,'b')-1;
label=put(start,date9.)||' '||cats(year(end+1)-1,'/',year(end+1));
output;
start=intnx('month3',start,1,'b');
end;

hlo='O';
label='** undefined FY **';
output;

run;

proc format library=work cntlin=ctrl;
run;

data _null_;
input type $ date anydtdte.;
put date= FISCYRPD.;
datalines;
A 01-Oct-09
A 01-Jan-10
A 01-Apr-10
A 01-Jul-10
A 01-Oct-10
A 01-Jan-11
A 01-Apr-11
A 01-Jul-11
;
run;
Sunny
Calcite | Level 5
data fy;
input type $ date :date9.;
format date date9.;
datalines;
A 01Oct09
A 01Jan10
A 01Apr10
A 01Jul10
A 01Oct10
A 01Jan11
A 01Apr11
A 01Jul11
A 01oct08
A 01jan09
A 01apr09
A 01jul09
A 01oct09
;
run;
data fy1;
set fy;
if MONTH(DATE) in (10, 11,12) then
do;
preyear = YEAR(DATE);
postyear = YEAR(DATE) + 1;
fiscal = compbl(preyear||"/"||postyear);
if qtr(date) = 4 then qtr = 1;
end;
else if MONTH(DATE) not in (10, 11,12) then
do;
preyear = YEAR(DATE) - 1;
postyear = YEAR(DATE);
fiscal = compbl(preyear||"/"||postyear);
qtr = qtr(date) + 1;
end;
drop P:;
RUN;

This way you can get what u r looking out... i m not sure whether you require qtr, but i have calculated it.

do let me know in case you require anything else.
coba
Calcite | Level 5
Thank you very much Sunny for your solution, specially now that my SAS skills did not allow me to get this solution.
Patrick
Opal | Level 21
Hi

Sorry to insist here a bit but I believe that using a format is a more versatile approach.

Such FY categories tend to be needed over and over again for grouping and reports. That's why I think it's better to use a format which has to be created once instead of re-calculating everything over and over again.

You can store a format in a permanent catalogue - also in one which is always available when you start SAS.

Just to give you an idea of how to use a format:

/* create a format for FY */
data ctrl;
start='01oct1990'd;
retain fmtname 'FISCYRPD' type 'n';
do while (start lt '01oct2050'd);
end=intnx('month3',start,1,'b')-1;
label=put(start,date9.)||' '||cats(year(end+1)-1,'/',year(end+1));
output;
start=intnx('month3',start,1,'b');
end;

hlo='O';
label='** undefined FY **';
output;

run;

proc format library=work cntlin=ctrl;
run;

/* source data */
data have;
input type $ date anydtdte.;
datalines;
A 01-Oct-09
A 01-Jan-10
A 01-Apr-10
A 01-Jul-10
A 01-Oct-10
A 01-Jan-11
A 01-Apr-11
A 01-Jul-11
;
run;

/* print source using FY format */
title 'format applied on existing date var';
proc print data=have;
format date FISCYRPD.;
run;

/* add character var containing fiscal year string */
data want;
set have;
FY=put(date,FISCYRPD.);
run;

title 'additional var';
proc print data=want;
run;

HTH
Patrick
data_null__
Jade | Level 19
Consider using the SAS date functions. INTNX can find the start date of the fiscal year, using a shifted interval. Then fiscal year quarter can be determined with INTCK.

[pre]
data test;
input type:$1. date:date.;
fy0 = intnx('year.10',date,0,'B'); *start date of fiscal year;
length fy $9;
fy = catx('/',year(fy0),year(fy0)+1);
fyQT = intck('QTR',fy0,date)+1;
format date date. fy0 date.;
cards;
A 01oct08
A 01jan09
A 01apr09
A 01jul09
A 01oct09
B 10oct09
B 23jan10
B 14apr10
B 04jul10
B 16Oct10
;;;;
run;
proc print;
by type;
id type;
run;
[/pre]

To produce this which I think is what the OP wanted.

[pre]
type date fy0 fy fyQ

A 01OCT08 01OCT08 2008/2009 1
01JAN09 01OCT08 2008/2009 2
01APR09 01OCT08 2008/2009 3
01JUL09 01OCT08 2008/2009 4
01OCT09 01OCT09 2009/2010 1

B 10OCT09 01OCT09 2009/2010 1
23JAN10 01OCT09 2009/2010 2
14APR10 01OCT09 2009/2010 3
04JUL10 01OCT09 2009/2010 4
16OCT10 01OCT10 2010/2011 1
[/pre]
coba
Calcite | Level 5
This is also an excellent solution.
Thank you for sharing your SAS skills with me and hopefully others will profit as well.
Sunny
Calcite | Level 5
Well.. see how many ways are there to solve a single problem. Thanks a lot for increasing my sas skills...

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
  • 12 replies
  • 2458 views
  • 0 likes
  • 6 in conversation