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;
... View more