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

I have to set start_date and end_date from the cycle date.

If the Cycle Date: 28th April 2018 then the:

 

  • Start Month: Apr 2016 – Exactly 2 years backdated. the below code I have fix for that.

SAS CODE: 

%let Curr_Date= '28APR2018'd;

start_date=intnx('year', &Curr_Date, -2,"sameday");

  

Similarly the end_date should always be Jan with respective year from cycle date and fiscal year

For Example:

 

  • End_date: Jan 2016 –>   should always be Jan 2016 for the fiscal year2017 [Apr2016 to Mar2017]
  • End_date: Jan 2018 –>   should always be Jan 2018 for the fiscal year2019 [Apr2018 to Mar2019]

Can you please provide me some sample code how to fix it?

1 ACCEPTED SOLUTION

Accepted Solutions
mkeintz
PROC Star

Is this a correct understanding of your requirements?

 

  1. Every sample date belongs to a fiscal year (01aprxxxx-31marxxxy) where xxxy=xxxx+1.
  2. start month is 2 years (sameday) preceding the sample date.  (It's really start day, not start month, right?).
  3. enddate is 01janxxxx,

If these are your rules then you could construct the first day of the fiscal year containing sample_date as

fy_start_date=intnx('year.4',sample_date,0,'begin');

 

The YEAR.4 interval is a year beginning on April 01.  So the above adds 0 fiscal years to sample_date, and aligns the result to the beginning of the fiscal year - i.e. 01APR.  Then it's easy to get 01JAN of the same year.  For instance:

enddate=mdy(1,1,year(fy_start_date);

 

Or in a single assignment

enddate=mdy(1,1,year(intnx('year.4',sample_date,0,'begin')));
--------------------------
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

--------------------------

View solution in original post

5 REPLIES 5
Patrick
Opal | Level 21

@Spintu

Not sure that I've understood the logic you're after. Does below return your desired result?

data test;
  format 
    have_dt date9.
    start_fisc_dt date9.
    want_dt date9.
    ;
  input have_dt :date9.;

  /* start of fiscal year for have_dt */
  start_fisc_dt=intnx('year.4',have_dt,0,'b');

  /* 01 january date in calendar date of start date of fiscal year */
  want_dt=intnx('year.4',start_fisc_dt,0,'b');

  datalines;
01JAN2016
31MAR2016
01APR2016
31DEC2016
01JAN2017
;
run;

Capture.JPG

Spintu
Quartz | Level 8

Hi

Actually my concern is  I have a  Cycle Date: 28th April 2018.

 

Start_date should be trigger from  : Apr 2016 – Exactly 2 years backdated

 

end_date  should be dynamically change based on fiscal year.

Example:

 

End_date: Jan 2017 – should always be Jan 2017 for the fiscal year2018 [Apr2016 to Mar2017]

Again the end_date will change from [Apr 2017 to Mar2018] the end_date it should be : Jan2018

 

Does that make sense for your understanding.

I wrote the below SAS code , however I m not sure end_date output it has be resolve two times in dynamically

 

 

SAS CODE:

 

%let Curr_Date= '28APR2018'd;
%let sysyear= %sysfunc(year("&Curr_Date"d));
%put  &sysyear;

 

data test;
file print;
format date end_date start_date date9.;

do month=1 to 12;
   start_date=intnx('year', &Curr_Date, -2,"sameday");

   date=mdy(month,1,&sysyear);

   fedfiscalyear = year(start_date) + (month(date) le 3);
   end_date="01JAN&sysyear"d;

   put date= mmddyy10. fedfiscalyear=   ;

end;
    call symputx("startdate",start_date);
 call symputx("end_date",end_date); 
run;
%put &startdate. &end_date.;

Patrick
Opal | Level 21

@Spintu

I'm still not sure that I understand. Using the data I've posted why don't you show us what the desired want_dt should be when using your logic.

 

From what you describe you should be able to use the INTNX() function also for your end_date calculation.

 

 

 

mkeintz
PROC Star

Is this a correct understanding of your requirements?

 

  1. Every sample date belongs to a fiscal year (01aprxxxx-31marxxxy) where xxxy=xxxx+1.
  2. start month is 2 years (sameday) preceding the sample date.  (It's really start day, not start month, right?).
  3. enddate is 01janxxxx,

If these are your rules then you could construct the first day of the fiscal year containing sample_date as

fy_start_date=intnx('year.4',sample_date,0,'begin');

 

The YEAR.4 interval is a year beginning on April 01.  So the above adds 0 fiscal years to sample_date, and aligns the result to the beginning of the fiscal year - i.e. 01APR.  Then it's easy to get 01JAN of the same year.  For instance:

enddate=mdy(1,1,year(fy_start_date);

 

Or in a single assignment

enddate=mdy(1,1,year(intnx('year.4',sample_date,0,'begin')));
--------------------------
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

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

start month is 2 years (sameday) preceding the sample date.  (It's really start day, not start month, right?).

That's correct it is start day. Now I am able to calculate end date based on fiscal year. It is working fine for me.

thank you very much.  Sorry for delayed your response due to family matter.

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 5 replies
  • 887 views
  • 0 likes
  • 3 in conversation