Coding help

[sanjaymane7]

Hello Sir,

I am a new user and need help in coding. I have a one record which I want to break financial year wise but should not go beyond the RED. I have given the desire output for your reference.

Input
	TRAN_ID	NET_PREMIUM	NWP	RSD	RED
	ICM_NOV_22_807600	22,937	8,257	19-Nov-22	18-Nov-25
Output
	TRAN_ID	NET_PREMIUM	NWP	RSD	RED
	ICM_NOV_22_807600	22,937	8,257	19-Nov-22	31-Mar-23
	ICM_NOV_22_807600	22,937	8,257	01-Apr-23	31-Mar-24
	ICM_NOV_22_807600	22,937	8,257	01-Apr-24	31-Mar-25
	ICM_NOV_22_807600	22,937	8,257	01-Apr-25	18-Nov-25

[PaigeMiller]

Could you please provide a more detailed explanation?

In addition, please answer this:

How do we know the first record that you desire starts 19-Nov-22 (okay, I think I can figure that out) and ends 31-Mar-23 (I have no idea why 31-Mar-23 is what you want).

[sanjaymane7]

Hello sir, I want to start from RSD and end every line at the financial year as Our financial year is APR TO MAR. Hence, I want to end 31MAR

[Tom · Accepted solution]

First thing we have to do is convert your spreadsheet listing into actual SAS datsets so we have something we can program with.

data have;
 input TRAN_ID :$20. NET_PREMIUM :comma. NWP :comma. RSD :date. RED :date.;
 format RSD RED date9. NET_PREMIUM NWP comma.;
cards;
ICM_NOV_22_807600 22,937 8,257 19-Nov-22 18-Nov-25
;

data want; 
 input TRAN_ID :$20. NET_PREMIUM :comma. NWP :comma. RSD :date. RED :date.;
 format RSD RED date9. NET_PREMIUM NWP comma.;
cards;
ICM_NOV_22_807600 22,937 8,257 19-Nov-22 31-Mar-23
ICM_NOV_22_807600 22,937 8,257 01-Apr-23 31-Mar-24
ICM_NOV_22_807600 22,937 8,257 01-Apr-24 31-Mar-25
ICM_NOV_22_807600 22,937 8,257 01-Apr-25 18-Nov-25
;

So it looks like fiscal years start on first of April.  So that translates into the YEAR.4 interval.  We can use the INTCK() function find out how many year intervals are covered by your date range.  Then we can loop over that number and calculate the start/end dates of the fiscal year.  We just need to add code to make sure the first and last dates do not extend beyond the original range.

data test;
  set have;
  do offset=0 to intck('year.4',RSD,red);
     new_rsd = max(RSD,intnx('year.4',RSD,offset,'b'));
     new_red = min(RED,intnx('year.4',RSD,offset,'e'));
     format new_rsd new_red date9.;
     output;
  end;
  rename rsd=old_rsd new_rsd=RSD red=old_red new_red=RED ;
run;

Results:

[Ksharp]

Interesting question. I like it.

data have;
 input TRAN_ID :$20. NET_PREMIUM :comma. NWP :comma. RSD :date. RED :date.;
 format RSD RED date9. NET_PREMIUM NWP comma.;
cards;
ICM_NOV_22_807600 22,937 8,257 19-Nov-22 18-Nov-25
;

data want;
 set have;
 new_rsd=RSD;
 do date=RSD to RED;
  if (month(date)=4 and day(date)=1) or date=RED then do;
    new_red=ifn(date=RED,date,date-1);output;new_rsd=date;
  end;
 end;
 drop date RSD RED;
 format new_rsd new_red date9.;
run;