topic Re: How to remove duplicate values inside a group? in SAS Enterprise Guide

How to remove duplicate values inside a group?

Andalusia — Mon, 19 Apr 2021 07:16:34 GMT

I'm trying to do the following:

If a quarter occurs more then once in a type I want to keep only the quarter with the highest phase. The other ones need to be deleted.

This is the data I have (in reality much bigger!):

data have;
infile datalines;
input type $ quarter phase;
datalines;
K-11 202001 1
K-11 202101 2
K-11 202003
K-11 202004 3
K-12 202101 3
K-12 202102 1
K-12 202103 1
K-12 202103 1
K-13 202003 2
K-13 202103 3
K-13 202103 2
K-13 202103 2
;

This is the data I want:

K-11 202001 1
K-11 202101 2
K-11 202003
K-11 202004 3
K-12 202101 3
K-12 202102 1
K-12 202103 1
K-13 202003 2
K-13 202103 3
;

Re: How to remove duplicate values inside a group?

Patrick — Mon, 19 Apr 2021 07:29:34 GMT

Here you go.

data have;
  infile datalines truncover;
  input type $ quarter phase;
  datalines;
K-11 202001 1
K-11 202101 2
K-11 202003
K-11 202004 3
K-12 202101 3
K-12 202102 1
K-12 202103 1
K-12 202103 1
K-13 202003 2
K-13 202103 3
K-13 202103 2
K-13 202103 2
;

proc sort data=have out=want;
  by type quarter descending phase;
run;

data want;
  set want;
  by type quarter;

  if first.quarter;
run;

proc print data=want;
run;

Re: How to remove duplicate values inside a group?

Patrick — Mon, 19 Apr 2021 07:33:10 GMT

Or here a 2nd option to achieve the same. Bit special but it has the advantage that SAS "knows" that want is sorted which can be beneficial for performance for downstream processing requiring data sorted this way.

data have;
  infile datalines truncover;
  input type $ quarter phase;
  datalines;
K-11 202001 1
K-11 202101 2
K-11 202003
K-11 202004 3
K-12 202101 3
K-12 202102 1
K-12 202103 1
K-12 202103 1
K-13 202003 2
K-13 202103 3
K-13 202103 2
K-13 202103 2
;

proc sort data=have out=want;
  by type quarter descending phase;
run;

proc sort data=want out=want nodupkey;
  by type quarter;
run;

proc print data=want;
run;