Hi Community,
I have the following customer data that has multiple records per customer (this is just a sample):
Customer_ID | Name | Status | Status_Date |
1234 | ABC | Open | 01/02/2016 |
1234 | ABC | Closed | 06/07/2017 |
1234 | ABC | Open | 01/15/2018 |
3421 | XYZ | Open | 09/22/2014 |
3421 | XYZ | Pending | 09/06/2015 |
3421 | XYZ | Closed | 06/14/2016 |
3421 | XYZ | Open | 06/12/2017 |
9120 | BBB | Open | 02/05/2016 |
9120 | BBB | Open | 08/11/2017 |
9120 | BBB | Closed | 09/01/2017 |
And I need to create only one line that summarizes the needed information from the above sample data, as follows:
Customer_ID | Name | Ever_Pending_Close_0_3_Years | Ever_Pending_Closed_3_5_Years | Open_Now |
1234 | ABC | Yes | No | Yes |
3421 | XYZ | No | Yes | Yes |
9120 | BBB | Yes | No | No |
The periods above are:
Ever_Pending_Close_0_3_Years = ever closed or pending in the last three years from today?
Ever_Pending_Closed_3_5_Years = ever closed or pending in the last FIVE years, but not in the last three years (between 3-5 years) from today?
Thanks!!
Do it stepwise, assign groups, then summarise based on the groups. Just a rough idea here (post test data in the form of a datastep, not here to type that in):
data inter; set have; retain open_date; length group $200; by customer_id; if first.customer_id then open_date=status_date; if status="Open" and open_date=. then open_date=status_date; if status="Closed" then do; group=intnx('year',open_date,status_date); open_date=.; end; if last.customer_id and open_date ne . then group=open_now; run;
Then transpose up distinct records of customer_id and group.
data have;
input Customer_ID Name $ Status $ Status_Date :mmddyy10.;
format Status_Date mmddyy10.;
cards;
1234 ABC Open 01/02/2016
1234 ABC Closed 06/07/2017
1234 ABC Open 01/15/2018
3421 XYZ Open 09/22/2014
3421 XYZ Pending 09/06/2015
3421 XYZ Closed 06/14/2016
3421 XYZ Open 06/12/2017
9120 BBB Open 02/05/2016
9120 BBB Open 08/11/2017
9120 BBB Closed 09/01/2017
;
data want;
if 0 then set have;
Ever_Pending_Close_0_3_Years='NO ';
Ever_Pending_Closed_3_5_Years='NO ';
Open_Now='NO ';
do until(last.name);
set have;
by customer_id name notsorted;
if intnx('year', today(),-2,'s')<(Status_Date)<=today() and Status in ('Closed','Pending') then Ever_Pending_Close_0_3_Years='YES';
If intnx('year', today(),-4,'s')<(Status_Date)<=intnx('year', today(),-2,'s') and Status in ('Closed','Pending') then Ever_Pending_Closed_3_5_Years='YES';
if last.name and status='Open' then Open_Now='YES';
end;
drop Status Status_Date;
run;
Thanks Novinosrin
Everything seems working except that my date format is this: DATETIME26.6, which results in not calculating the periods at all.
Any workaround?
Thanks
Do you mean your status_date is a datetime value and not a date value?
In that case,
if intnx('year', today(),-2,'s')<datepart(Status_Date)<=today() and Status in ('Closed','Pending') then Ever_Pending_Close_0_3_Years='YES';
If intnx('year', today(),-4,'s')<datepart(Status_Date)<=intnx('year', today(),-2,'s') and Status in ('Closed','Pending') then Ever_Pending_Closed_3_5_Years='YES';
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: