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 |
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;
Is there a cut-off date to identify Fiscal Year ?
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....
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;
Hi Kia....Thanks for your help......it worked perfectly.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.