re: Add Records to Dataset

[twildone]

Hi.....I am trying to add records for each client based on the Fiscal Year (beginning) of the the Effective Dates and Expiry Dates. If the clinet only has a Effective Date, then I want to increment from the Fiscal Year of the Effective Date up to the Current Fiscal Year (todays date). If the client only has a Expiry Date, then increment from the Fiscal Year of the Expiry Date to the next Fiscal Year. NOTE: whenever there is a Expiry Date, the End for the increments is alway the following Fiscal Year from the Fiscal Year of the Expiry Date. How can new records be added and increment by a new Fiscal Year. Thanks.

Have:

Client	Status	Date
13223	Effective	20140503
13223	Expired	20160723
14239	Effective	20120124
14239	Expired	20150324
16982	Effective	20150112
17566	Expired	20130321

Want:

Client	FiscalYear	Type
13223	2014	New
13223	2015	Existing
13223	2016	Existing
13223	2017	Exit
14239	2011	New
14239	2012	Existing
14239	2013	Existing
14239	2014	Existing
14239	2015	Exit
16982	2014	New
16982	2015	Existing
16982	2016	Existing
17566	2012	Existing
17566	2013	Exit

[Ksharp]

Is there a cut-off date to identify Fiscal Year ?

[twildone]

Hi Xia....the cutoff date for the fiscal year can be assumed to be the date that the sas program is run. I will try your suggestion and let you know if it works as I am hoping it does....thanks....

[Ksharp · Accepted solution]

Assuming the cut-off data for fiscal year is April-01 . And there are at most two obs for each Client .

data have;
infile cards expandtabs truncover;
input Client	Status : $20.	Date : yymmdd10.;
format date yymmddn8.;
cards;
13223	Effective	20140503
13223	Expired	20160723
14239	Effective	20120124
14239	Expired	20150324
16982	Effective	20150112
17566	Expired	20130321
;
run;

data have;
 set have;
 year=year(intnx('year.4',date,0));
run;
data want;
 merge have have(keep=client year status
 rename=(client=_client year=_year status=_status) firstobs=2);
 length type $ 20;
 type=ifc(Status='Effective','new','existing');fiscal_year=year;output;
 
 if client=_client then do;
  do fiscal_year=year+1 to _year-1;
   type='existing';
   output;
  end;
 end;
 
 else do;
  if Status='Expired' then do;
   fiscal_year=year+1;type='exit';output;
  end;
  else do;
   do fiscal_year=year+1 to year(today());
    type='existing';output;
   end;
  end;
 end;
keep client type fiscal_year;
run;

[twildone]

Hi Kia....Thanks for your help......it worked perfectly.