Help using Base SAS procedures

Keeping variables with Open/Closed orders

Accepted Solution Solved
Reply
Contributor
Posts: 46
Accepted Solution

Keeping variables with Open/Closed orders

[ Edited ]

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!


Accepted Solutions
Solution
‎06-12-2018 03:25 PM
Super User
Posts: 10,787

Re: Keeping variables with Open/Closed orders

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


All Replies
Super User
Posts: 13,583

Re: Keeping variables with Open/Closed orders

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.

Contributor
Posts: 46

Re: Keeping variables with Open/Closed orders

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

Solution
‎06-12-2018 03:25 PM
Super User
Posts: 10,787

Re: Keeping variables with Open/Closed orders

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;
Contributor
Posts: 46

Re: Keeping variables with Open/Closed orders

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,

Super User
Posts: 13,583

Re: Keeping variables with Open/Closed orders

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.

 

Super User
Posts: 10,787

Re: Keeping variables with Open/Closed orders

COMPRESS() can create a new condition variable.

 

Condition=compress(id,,'kd');
☑ This topic is solved.

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

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