SAS EG (SQL) deleting rows where max value in one column using double group by

Question

I need to delete all the rows with a max value of duty_per_id where the rotn_prng_nbr and empl_nbr are the same (not the same to each other, but the max where of all of the rows where those two remain constant). From the table below it should delete rows 3,7 and 9.

rotn_prng_nbr	empl_nbr	duty_perd_id
B93	12	1
B93	12	2
B93	12	3
B21	12	1
B21	12	2
B21	12	3
B21	12	4
B21	18	1
B21	18	2

using SAS EG.

score 0 · Answer 1 · answered Jun 10 '21 at 13:49

0

One method uses a correlated subquery:

select t.*
from t
where t.duty_perd_id < (select max(t2.duty_perd_id)
                        from t t2
                        where t2.rotn_prng_nbr = t.rotn_prng_nbr and
                              t2.empl_nbr = t.empl_nbr
                       );

answered Jun 10 '21 at 13:49

Gordon Linoff

1,242,037
58
646
786

sorry for the late response here, but what exactly is t2 in this code? – Michael Ancel Jun 14 '21 at 14:22
@MichaelAncel . . . `t2` is a table alias for the table you are using. It is defined in the subquery to enable the correlation clause. – Gordon Linoff Jun 14 '21 at 14:56
thanks! I got it to work, but this solution is still really slow to run. I opened a new post if you are interested, but either way I appreciate the help! – Michael Ancel Jun 14 '21 at 15:59
https://stackoverflow.com/questions/67973562/sas-eg-sql-deleting-rows-where-max-value-in-one-column – Michael Ancel Jun 14 '21 at 15:59

SAS EG (SQL) deleting rows where max value in one column using double group by

1 Answers1