BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
altijani
Quartz | Level 8

Greetings Community,

 

I have the following data, which takes four combinations:

1. Not having a closed order (Open) ==> keep the latest observation

2. Having an open and then closed order in last month ==> keep the open order's observation

3. Having a closed order ==> keep the latest observation

 

Here is what I have:

Condition #

ID

Report MonthStatus DateStatus
1900102/28/201802/10/2018Open
1900103/31/201803/06/2018Open
1900104/30/201804/16/2018Open
2123401/31/201801/15/2018Open
2123402/28/201802/10/2018Open
2123403/31/201803/07/2018Open
2123404/30/201804/12/2018Closed
21234CN04/30/201804/17/2018Closed
3567801/31/201801/15/2018Open
3567802/28/201802/10/2018Open
35678CN04/30/201803/19/2018Closed
3567804/30/201804/12/2018Open
44444CN02/28/201802/12/2018Closed
44444CN03/31/201803/16/2018Closed
44444CN04/30/201804/09/2018Closed

 

Here is what I want:

Condition #

ID

Report MonthStatus DateStatus
1900104/30/201804/16/2018Open
21234CN04/30/201804/12/2018Closed
3567804/30/201804/12/2018Open
44444CN04/30/201804/09/2018Closed

 

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User
data have;
infile cards expandtabs ;
input (Condition ID ReportMonth	StatusDate	Status) (: $20.);
cards;
1	9001	02/28/2018	02/10/2018	Open
1	9001	03/31/2018	03/06/2018	Open
1	9001	04/30/2018	04/16/2018	Open
2	1234	01/31/2018	01/15/2018	Open
2	1234	02/28/2018	02/10/2018	Open
2	1234	03/31/2018	03/07/2018	Open
2	1234	04/30/2018	04/12/2018	Closed
2	1234CN	04/30/2018	04/17/2018	Closed
3	5678	01/31/2018	01/15/2018	Open
3	5678	02/28/2018	02/10/2018	Open
3	5678CN	04/30/2018	03/19/2018	Closed
3	5678	04/30/2018	04/12/2018	Open
4	4444CN	02/28/2018	02/12/2018	Closed
4	4444CN	03/31/2018	03/16/2018	Closed
4	4444CN	04/30/2018	04/09/2018	Closed
;
run;

data want;
n=0;
 do i=1 by 1 until(last.Condition);
  set have;
  by Condition;
  if Status='Open' then idx_open=i;
  if Status='Closed' then do;
   idx_Closed=i; 
   n+1;
   if n=1 then do;r=ReportMonth;s=StatusDate;end;
  end;
 end;

 if . < idx_open < idx_Closed  then do;
   ReportMonth=r;StatusDate=s;
 end;
drop n i r s idx_:;
 run;

View solution in original post

6 REPLIES 6
ballardw
Super User

It might help to indicate which condition, 1, 2 or 3 each of those kept records represents.

 

And the fact that you might have multiple values of ID that are treated the same needs some definition such as 1234 and 1234CN, 5678 and 5678CN. Since date sequence is important in processing this data some approaches would likely be looking at sorting the data but your shown behavior for 5678CN means that is not going to work without some additional steps.

altijani
Quartz | Level 8

Thanks . I just updated the data with the condition number.

Ksharp
Super User
data have;
infile cards expandtabs ;
input (Condition ID ReportMonth	StatusDate	Status) (: $20.);
cards;
1	9001	02/28/2018	02/10/2018	Open
1	9001	03/31/2018	03/06/2018	Open
1	9001	04/30/2018	04/16/2018	Open
2	1234	01/31/2018	01/15/2018	Open
2	1234	02/28/2018	02/10/2018	Open
2	1234	03/31/2018	03/07/2018	Open
2	1234	04/30/2018	04/12/2018	Closed
2	1234CN	04/30/2018	04/17/2018	Closed
3	5678	01/31/2018	01/15/2018	Open
3	5678	02/28/2018	02/10/2018	Open
3	5678CN	04/30/2018	03/19/2018	Closed
3	5678	04/30/2018	04/12/2018	Open
4	4444CN	02/28/2018	02/12/2018	Closed
4	4444CN	03/31/2018	03/16/2018	Closed
4	4444CN	04/30/2018	04/09/2018	Closed
;
run;

data want;
n=0;
 do i=1 by 1 until(last.Condition);
  set have;
  by Condition;
  if Status='Open' then idx_open=i;
  if Status='Closed' then do;
   idx_Closed=i; 
   n+1;
   if n=1 then do;r=ReportMonth;s=StatusDate;end;
  end;
 end;

 if . < idx_open < idx_Closed  then do;
   ReportMonth=r;StatusDate=s;
 end;
drop n i r s idx_:;
 run;
altijani
Quartz | Level 8

Thanks . While this code works for this sample data, I do not actually have the variable condition in my data. I created it here because it was requested by one of the members. Any idea on how to create this "condition" variable, so that your code will work in my data?

 

Thanks,

ballardw
Super User

You still need to describe why and how the ID variable with different values is to be treated as the same.

 

I had asked about adding the condition you were treating a number of records because I did not see how the simple values of the ID variable could yield the desired results because they appeared to involve at least two different actual values of the "ID" variable. So I asked for more of a description on how an "ID" has two (or is it more) values that are treated as essentially identical. For instance is it possible that somewhere lurking the data is something like "123CN" that is supposed to also be the "same" id as 1234? Or 1234PQ? or others.

 

I think that you might need to add an additional variable to actually uniquely identify groups of ID values into a single "group id" for processing.

 

Ksharp
Super User

COMPRESS() can create a new condition variable.

 

Condition=compress(id,,'kd');

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 858 views
  • 0 likes
  • 3 in conversation