DATA Step, Macro, Functions and more

re: Add Records to Dataset

Accepted Solution Solved
Reply
Regular Contributor
Posts: 222
Accepted Solution

re: Add Records to Dataset

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

Accepted Solutions
Solution
‎08-15-2016 12:12 PM
Super User
Posts: 9,691

Re: re: Add Records to Dataset

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;

View solution in original post


All Replies
Super User
Posts: 9,691

Re: re: Add Records to Dataset

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

Regular Contributor
Posts: 222

Re: re: Add Records to Dataset

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....

Solution
‎08-15-2016 12:12 PM
Super User
Posts: 9,691

Re: re: Add Records to Dataset

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;

Regular Contributor
Posts: 222

Re: re: Add Records to Dataset

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 313 views
  • 0 likes
  • 2 in conversation