How to delete duplicate rows by two columns

[lutherh]

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                                   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!

[bnawrocki]

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?

[alooo]

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