How do I choose one of the two duplicates by ranking? I need an automated code.

[Pili1100]

My customer dataset contains duplicates: Answer_date differs, but Answer_month is the same. 

I need to choose one customer rows according to the following

1. When Answer_value < 7 : Criteria_met = 'Yes'
2. If both rows have Criteria_met = 'Yes'
Then choose then one with Answer_string NE ' '

3. Both rows have Criteria_met = 'Yes' and Answer_string NE ' '
Then choose then one with max date.

I need an automated code 😁

Dataset have1 contains all customer rows: contains duplicates.

Dateset have2 contains all customer rows with Criteria_met = 'Yes': contains duplicates

Dataset want contains the rows I want according to my ranking. However I need an automated code 😊

DATA WORK.Have1;

RETAIN
ID Name Answer_Date Answer_month answer_value Answer_string criteria_met
;
	LENGTH Name Answer_string $50;
	FORMAT Answer_Date YYMMDD10.;
INFILE DATALINES DELIMITER =','; 
INPUT ID $ Name $ Answer_Date  YYMMDD10. Answer_month $ answer_value Answer_string $ criteria_met $ ;
CARDS; 
1,Jessica Beatrice Fletcher,2021-04-15,2021M04,10,Very good,No
1,Jessica Beatrice Fletcher,2021-04-22,2021M04,6,OK,Yes
2,Seth Hazlett,2021-04-15,2021M04,5,Bad,Yes
2,Seth Hazlett,2021-04-22,2021M04,4, ,Yes
3,Mort Metzger,2021-04-15,2021M04,7,OK,No
3,Mort Metzger,2021-04-22,2021M04,7,I'm happy,No
4,Eve Simpson,2021-04-15,2021M04,4,No good,Yes
4,Eve Simpson,2021-04-22,2021M04,2,Not working,Yes
5,Lauretta Speigel,2021-04-15,2021M04,8, ,No
5,Lauretta Speigel,2021-04-22,2021M04,8, ,No
6,Amos Tupper,2021-04-15,2021M04,10,Happy,No
6,Amos Tupper,2021-04-22,2021M04,10,Very Good,No
7,Dennis Stanton,2021-04-15,2021M04,3,Needs improvement,Yes
7,Dennis Stanton,2021-04-22,2021M04,6,Still needs improvement ,No
8,Michael Hagarty,2021-04-15,2021M04,2, ,Yes
8,Michael Hagarty,2021-04-22,2021M04,9, ,No
;

RUN;
DATA have2;
SET have1;
	WHERE criteria_met = 'Yes';
RUN;
DATA want;
SET have2;
IF ID IN ('1', '7', '8') 		  		 THEN OUTPUT;
IF ID = 2 AND Answer_string NE '' 		 THEN OUTPUT;
IF ID = 4 AND Answer_date = '22APR2021'd THEN OUTPUT;
RUN;

[andreas_lds]

Is the number of obs for each id always two?

[Pili1100]

It can probably vary but it's often two rows per customer and month

[andreas_lds]

---

@Pili1100 wrote:

It can probably vary but it's often two rows per customer and month

---

Then you should modify have1 to contain such cases.

[andreas_lds]

Spoiler

---

@Pili1100 wrote:

My customer dataset contains duplicates: Answer_date differs, but Answer_month is the same. 

I need to choose one customer rows according to the following

1. When Answer_value < 7 : Criteria_met = 'Yes'
2. If both rows have Criteria_met = 'Yes'
Then choose then one with Answer_string NE ' '

3. Both rows have Criteria_met = 'Yes' and Answer_string NE ' '
Then choose then one with max date.

I need an automated code 😁

Dataset have1 contains all customer rows: contains duplicates.

Dateset have2 contains all customer rows with Criteria_met = 'Yes': contains duplicates

Dataset want contains the rows I want according to my ranking. However I need an automated code 😊

DATA WORK.Have1;

RETAIN
ID Name Answer_Date Answer_month answer_value Answer_string criteria_met
;
	LENGTH Name Answer_string $50;
	FORMAT Answer_Date YYMMDD10.;
INFILE DATALINES DELIMITER =','; 
INPUT ID $ Name $ Answer_Date  YYMMDD10. Answer_month $ answer_value Answer_string $ criteria_met $ ;
CARDS; 
1,Jessica Beatrice Fletcher,2021-04-15,2021M04,10,Very good,No
1,Jessica Beatrice Fletcher,2021-04-22,2021M04,6,OK,Yes
2,Seth Hazlett,2021-04-15,2021M04,5,Bad,Yes
2,Seth Hazlett,2021-04-22,2021M04,4, ,Yes
3,Mort Metzger,2021-04-15,2021M04,7,OK,No
3,Mort Metzger,2021-04-22,2021M04,7,I'm happy,No
4,Eve Simpson,2021-04-15,2021M04,4,No good,Yes
4,Eve Simpson,2021-04-22,2021M04,2,Not working,Yes
5,Lauretta Speigel,2021-04-15,2021M04,8, ,No
5,Lauretta Speigel,2021-04-22,2021M04,8, ,No
6,Amos Tupper,2021-04-15,2021M04,10,Happy,No
6,Amos Tupper,2021-04-22,2021M04,10,Very Good,No
7,Dennis Stanton,2021-04-15,2021M04,3,Needs improvement,Yes
7,Dennis Stanton,2021-04-22,2021M04,6,Still needs improvement ,No
8,Michael Hagarty,2021-04-15,2021M04,2, ,Yes
8,Michael Hagarty,2021-04-22,2021M04,9, ,No
;

RUN;
DATA have2;
SET have1;
	WHERE criteria_met = 'Yes';
RUN;
DATA want;
SET have2;
IF ID IN ('1', '7', '8') 		  		 THEN OUTPUT;
IF ID = 2 AND Answer_string NE '' 		 THEN OUTPUT;
IF ID = 4 AND Answer_date = '22APR2021'd THEN OUTPUT;
RUN;

---

This could be a solution:

proc sort data=have1 out=sorted;
   by id descending answer_date;
   where criteria_met = 'Yes';
run;

data want;
   set sorted;
   by id;
   
   retain id_kept;
   drop id_kept;
   
   if first.id then id_kept = 0;
      
   if first.id and last.id then do;
       /* keep observation if it is the only one for an id */
      output;
   end;
   else do;
      /* the first obs having answer_string ^= " " will be kept */
      if not missing(Answer_string) and id_kept = 0 then do;
         id_kept = 1;
         output;
      end;
   end;
run;

[FreelanceReinh]

Hello @Pili1100,

To perform selections like this you can apply BY-group processing in a DATA step (using first.variable or last.variable) to an appropriately sorted version of the input dataset. The ORDER BY clause of PROC SQL is particularly suitable for implementing hierarchical sort criteria. The "sorted version" of the input dataset can be a view (to save disk space). Here's an example:

proc sql;
create view _tmp as
select * from have1
where .z<answer_value<7 & criteria_met='Yes'
order by id, answer_month, missing(answer_string), answer_date desc;
quit;

data want;
set _tmp;
by id answer_month;
if first.answer_month;
run;

Note that I interpreted "1. When Answer_value < 7 : Criteria_met = 'Yes'" as "answer_value has a non-missing value < 7 and criteria_met = 'Yes'" (in view of obs. 14 of HAVE1, which has answer_value=6, yet criteria_met = 'No'). Feel free to modify the WHERE clause if this is not correct.

[mkeintz]

Why is your desired output for ID=4  the second observation (4/22/2021), when the first observation (4/15/2021) has an answer_value<7?

Does satisfying rule 1  (answer<7 and criteria_met='Yes') not alleviate the need to assess rules 2 and 3?

I can see that rule 3 (the latest answer_string^=' ') subsumes rule 2 (answer_string^=' '),.but please clarify the status of rule 1.