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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.