Date:         Fri, 13 Dec 2002 20:24:12 -0500
Reply-To:     "Droogendyk, Harry" <Harry.Droogendyk@CIBC.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Droogendyk, Harry" <Harry.Droogendyk@CIBC.COM>
Subject:      Re: how do I delte the second record which is duplicate of the fi
              rst record?
Content-Type: text/plain; charset="iso-8859-1"

Ya Huang pointed out the solution I posted resulted in two legitimate
records being lost, apologies.

Note the change to the IF statement, no need to check first.stateid:


data new ( drop = _: );
  set a;
    by stateid locid;

  _flg1 = lag(flg1);
  _flg2 = lag(flg2);

   if not(first.locid) and not(missing(_flg1) + missing(_flg2)) then delete;
run;

The SAS System                                                  09:53
Friday, December 13, 2002  11

Obs    stateid    locid    flg1    flg2

  1     13748     00009     87      *
  2     13748     00010
  3     22054     00000
  4     22054     00001     86      *
  5     22054     00022
  6     22054     00023
  7     22054     00024
  8     22054     00025
  9     22054     00026
 10     29334     00200     87      *
 11     29334     00205
 12     33740     00001
 13     33740     00002     87      *
 14     33740     00003
 15     33740     00004
 16     33740     00005     87      *
 17     33740     00006
 18     33740     00008
 19     33740     00008

                -----Original Message-----
                From:   Droogendyk, Harry
                Sent:   December 13, 2002 6:25 PM
                To:     'SAS-L'
                Subject:        RE: how do I delte the second record which
is duplicate of the first record?

                Assuming the second observation of stateID / locID should
stick around if EITHER of flg1 -OR- flg2 are populated.  If it should be if
BOTH flg1 -AND- flg2 are populated change the "+" sign to "*" in the IF
statement.

                Note that the lag statements MUST be executed for each data
step iteration.


                proc sort data = a;
                      by stateid locid descending flg1 descending flg2;
                run;

                data new ( drop = _: );
                  set a;
                    by stateid locid;

                  _flg1 = lag(flg1);
                  _flg2 = lag(flg2);

                   if not(first.stateid and first.locid) and
not(missing(_flg1) + missing(_flg2)) then delete;
                run;

                proc print;run;

                The SAS System
09:53 Friday, December 13, 2002   9

                Obs    stateid    locid    flg1    flg2

                  1     13748     00009     87      *
                  2     13748     00010
                  3     22054     00000
                  4     22054     00001     86      *
                  5     22054     00023
                  6     22054     00024
                  7     22054     00025
                  8     22054     00026
                  9     29334     00200     87      *
                 10     29334     00205
                 11     33740     00001
                 12     33740     00002     87      *
                 13     33740     00004
                 14     33740     00005     87      *
                 15     33740     00006
                 16     33740     00008
                 17     33740     00008


                                -----Original Message-----
                                From:   Action Man
[mailto:wollo_desse@HOTMAIL.COM]
                                Sent:   December 13, 2002 6:00 PM
                                To:     SAS-L@LISTSERV.UGA.EDU
                                Subject:        how do I delte the second
record which is duplicate of the first record?

                                Hi SASLres;

                                I have the following problem:

                                when two records with the same stateID and
LocID but the first record has
                                values for FLG1 and FLG2.

                                I need to be able to remove or delte the
second record that was duplecate of
                                the first record.

                                How do I do that?



                                StateID         LocID            flg1
flg2

                                13748           00009             87      *
                                13748           00009   Remove this record
                                13748           00010
                                22054           00000
                                22054           00001             86      *
                                22054           00022
                                22054           00023
                                22054           00024
                                22054           00025
                                22054           00026
                                29334           00200             87      *
                                29334           00200   Remove this record
                                29334           00205
                                33740           00001
                                33740           00002             87      *
                                33740           00003
                                33740           00004
                                33740           00005             87      *
                                33740           00005   Remove this record
                                33740           00006
                                33740           00008
                                33740           00008   do not delete this.

                                Thank you in advance.
                                Wollo





_________________________________________________________________
                                Add photos to your e-mail with MSN 8. Get 2
months FREE*.

http://join.msn.com/?page=features/featuredemail