topic Re: How do I choose one of the two duplicates by ranking? I need an automated code. in SAS Programming

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

Pili1100 — Mon, 03 May 2021 08:41:41 GMT

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;

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

andreas_lds — Mon, 03 May 2021 10:45:36 GMT

Is the number of obs for each id always two?

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

Pili1100 — Mon, 03 May 2021 10:47:30 GMT

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

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

andreas_lds — Mon, 03 May 2021 10:49:30 GMT

@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.

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

andreas_lds — Mon, 03 May 2021 11:12:11 GMT

@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;

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

FreelanceReinh — Mon, 03 May 2021 11:13:33 GMT

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.

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

mkeintz — Mon, 03 May 2021 14:29:37 GMT

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.