topic Re: How to delete duplicate rows by two columns in SAS Programming

How to delete duplicate rows by two columns

lutherh — Mon, 08 Aug 2016 00:26:41 GMT

Hi All,

I have a dataset with four columns, of which the middle two have character variables from which I would like to delete duplicates. The first column is an identifier column, 'GuestNo'. The second and third columns are character columns listing type of candy bought before and after a diet program. The second column is the type of candy a guest bought before a diet program, the third column is the type of candy a guest bought after a diet program. I only want to keep the rows which show the unique candy choices before and after the diet program.

My data currently looks like this:

GuestNo Before After Cost

1 A 3

1 A 3.5

1 A 3

1 B 4

1 C 5

1 A 3.75

1 B 4.5

1 D 7

1 E 8

2 A 3.25

2 F 9.25

2 F 9.50

2 G 10

I want to know the amount of money guests spend on unique types of candy before and after the program. Thus, I would like to be able to create a new dataset that looks like this:

GuestNo Before After Cost

1 C 5

1 D 7

1 E 8

2 A 3.25

2 G 10

I have tried using a 'nodupkey' function, but it seems to only delete data from one column at a time, without the ability to read from multiple columns. Any help would be much apprecaited on the topic!

Re: How to delete duplicate rows by two columns

bnawrocki — Mon, 08 Aug 2016 18:54:37 GMT

Actually, NODUPKEY works on multiple columns also. If there's more than one record in which ALL the BY vars are the same, it will keep only the first of those records found. So, if you run -

PROC SORT NODUPKEY; 
   BY GUESTNO BEFORE AFTER;

- it should give you close to what you want.

I don't entirely understand the output you want, however -- why don't you want to keep row 4 - where CUSTNO=1, BEFORE=B, AFTER=blank? That particcular combination of values is not duplicated anywhere in the file, is it?

Re: How to delete duplicate rows by two columns

alooo — Fri, 16 Oct 2020 20:37:06 GMT

Hi I have encountered similar questions... have you figured it out at last? Thank you!