Newsgroups: bit.listserv.sas-l
Date:         Tue, 10 Nov 1992 14:01:01 CET
Reply-To:     Arnold Schick <SCHICK@DMRHRZ11.BITNET>
From:         Arnold Schick <SCHICK@DMRHRZ11.BITNET>
Subject:      Re: Thanks, new group id values from the old one

Hallo SAS-Lers,

last week, I searched a solution for new group id values from an old,
previously created data set. Many suggestions came from members of
the list, and solves the problem.

To complete this Thanks list here, I would like to express my
sincere and grateful thanks also to Joanne Benson, Mark D. H. Miller,
Linda Neden, and Matthew Zack, who sent me also solutions.

The question was:

from an old data set should have a new data set the id values with its
sequence ... , as in the following example:

        data old          data calc                data new
        X    Y     ID     X    Y     ID            X    Y     ID
        1    1     A      1    1     B             1    1     A
        2    2     A      1.2  1.7   B             1.2  1.7   A
        3    3     A      2    2     B             2    2     A
        2    5     B      3.1  3.6   B             3.1  3.6   A
        1    6     B      3    3     B             3    3     A
        0    4     B      1.9  4.9   C             1.9  4.9   B
        1.5  3     B      2    5     C             2    5     B
        6    2     D      2.5  5.1   C             2.5  5.1   B
        7    3     D      1    6     C             1    6     B
                          0.3  5.5   C             0.3  5.5   B
                          0    4     C             0    4     B
                          6    2     E             6    2     D
                          6.2  2.7   E             6.2  2.7   D
                          7    3     E             7    3     D

Here, I'll present two different solutions (1th, is for sorted data set
ONE and 2nd, for sorted/notsorted data set ONE) and hope that's possible
helpful for others - both running with SAS 6.04 and 6.07 -,

Arnold Schick  University of Marburg
-------------------------------------------------------------------------
   *                                                             ;
   * read in data from data set, old.;
   *  create a new variable for each identification key, and write;
   *   this variable to a new data set.                          ;
   *                                                             ;
   DATA OLD(KEEP=X Y ID)
        OLDID(KEEP=OLDID);
     RETAIN OLDID;
     INFILE CARDS;
     INPUT X Y ID $;
     IF _N_ EQ 1 THEN DO;
        OLDID=ID;
        OUTPUT OLDID;
     END;
     ELSE DO;
        IF ID NE OLDID THEN DO;
           OLDID=ID;
           OUTPUT OLDID;
        END;
     END;
     OUTPUT OLD;
   CARDS;
          1    1     A
          2    2     A
          3    3     A
          2    5     B
          1    6     B
          0    4     B
          1.5  3     B
          6    2     D
          7    3     D

     ;
   *                                                             ;
   * create new data set, calc.;
   *  here, read in new values because transformations of data set,;
   *  old, to data set, calc, not specified in original query.   ;
   *  create a new variable for each identification key, and write;
   *   this variable to a new data set.                          ;
   *                                                             ;
   DATA CALC(KEEP=X Y ID)
        CALCID(KEEP=CALCID);
     RETAIN CALCID;
     INFILE CARDS;
     INPUT X Y ID $;
     IF _N_ EQ 1 THEN DO;
        CALCID=ID;
        OUTPUT CALCID;
     END;
     ELSE DO;
        IF ID NE CALCID THEN DO;
           CALCID=ID;
           OUTPUT CALCID;
        END;
     END;
     OUTPUT CALC;
   CARDS;
        1    1     B
        1.2  1.7   B
        2    2     B
        3.1  3.6   B
        3    3     B
        5   11     A
        6   10     A
        1.9  4.9   C
        2    5     C
        2.5  5.1   C
        1    6     C
        6    2     D
        2    9     D
        5    1.7   D
        4    2.5   D
        7    3     D
   ;
   *                                                             ;
   * perform side-by-side merge of the identification keys from  ;
   *  the data sets, old and calc;
   *                                                             ;
   DATA BOTHID(KEEP=ID OLDID);
     MERGE CALCID(RENAME=(CALCID=ID)) OLDID;
     OUTPUT BOTHID;
   RUN;
   RUN;
   *                                                             ;
   * sort the data set, calc,;
   *  by the identification key.;
   *                                                             ;
   PROC SORT DATA=CALC;
     BY ID;
   RUN;
   *                                                             ;
   * sort the data set, bothid,;
   *  by the identification key from the data set, calc.;
   *                                                             ;
   PROC SORT DATA=BOTHID;
     BY ID;
   RUN;
   *                                                             ;
   * merge the data set, bothid, and the data set, calc,;
   *  by their common identification key.                        ;
   *                                                             ;
   DATA NEW(KEEP=X Y OLDID RENAME=(OLDID=ID));
     MERGE CALC BOTHID;
     BY ID;
     OUTPUT NEW;
   RUN;
   *                                                             ;
   * print data set, new.;
   *                                                             ;
   PROC PRINT DATA=NEW;
     VAR X Y ID;
     TITLE1 "DATA SET, NEW";
   RUN;
*------------------- end of program 1 ---------------------------;
The 2nd SAS program solves this for a NOTSORTED data set ONE:

 data old;
   input x y id $ ;
   cards;
          1    1     A
          2    2     A
          3    3     A
          5   11     K
          6   10     K
          2    5     B
          1    6     B
          0    4     B
          1.5  3     B
          6    2     D
          7    3     D
;
data calc;
  input x y id $ ;
  cards;
        1    1     B
        1.2  1.7   B
        2    2     B
        3.1  3.6   B
        3    3     B
        5   11     A
        6   10     A
        1.9  4.9   C
        2    5     C
        2.5  5.1   C
        1    6     C
        6    2     D
        2    9     D
        5    1.7   D
        4    2.5   D
        7    3     D
;
data one;
  set old (rename=(id=old_id));
  by old_id notsorted;
  if first.old_id;
  keep old_id;
run;
data two;
  set calc;
  by id notsorted;
  if first.id;
  keep id;
run;
data id_match;
  merge one two;
run;
data new;
  merge calc id_match; by id notsorted;
  id=old_id;
  drop old_id;
run;
title 'data ID-Match';
proc print data=id_match; run;
title 'data NEW with old ID-order';
proc print data=new; run;