/*----------------------------------------------------------*
 This is the code of each of the ten ways data could be
 merged as presented at PharmaSUG.  This code is provided
 "AS IS" with no implied warrenty.  In other words, use this
 code at your own risk.
 *----------------------------------------------------------*/


*--------------------------------------;
* The Data                             ;
*--------------------------------------;

data patdata;
   infile cards;
   input subject $ 1-6 trt_code $ 8;
cards;
124263 A
124264 A
124265 B
124266 B
;
run;

data adverse;
   infile cards;
   input subject $ 1-6 event $ 8-20;
cards;
124263 HEADACHE
124266 FEVER
124266 NAUSEA
124267 FRACTURE
;
run;


*--------------------------------------;
* Set Indexes for both datasets        ;
*--------------------------------------;

proc datasets library=work nolist nodetails nowarn;
   modify patdata;
      index create subject /unique;
   modify adverse;
      index create subject;
   quit;
run;


*--------------------------------------;
* 10. PEEK(C) and POKE -- This is OS   ;
*     dependent (works on Windows XP.  ;
*     Is known not to work on Unix.    ;
*--------------------------------------;

proc delete data=alldata0;
DATA alldata0;
   ARRAY f{&xpatdata.} $7 _TEMPORARY_;
      /*Store SUBJECT values*/
   ARRAY g{&xpatdata.} $1 _TEMPORARY_;
      /*Store TRT_CODE values*/
   LENGTH trt_code $1;

   DO i=1 TO &xpatdata.;
      SET patdata (RENAME=(trt_code=trt_dict));
      CALL POKE(COMPRESS(subject),
      ADDR(f[1])+((i-1)*7),7);
      CALL POKE(COMPRESS(trt_dict),
      ADDR(g[1])+((i-1)*1),1);
   END;

   DO i=1 TO &xadverse.;
      SET adverse;
      trt_code='';
      DO j=1 TO &xpatdata.;
         IF subject=PEEKC(ADDR(f[1])+((j-1)*7),7) THEN DO;
            trt_code=PEEKC(ADDR(g[1])+((j-1)*1),1);
            OUTPUT;
         END;
         IF ^MISSING(trt_code) THEN LEAVE;
      END;
      IF MISSING(trt_code) THEN OUTPUT;
   END;

   DROP i j trt_dict;

RUN;


*--------------------------------------;
* 9. Merge with Update                 ;
*--------------------------------------;

proc delete data=alldata0;
DATA alldata0 (DROP=_trt_code);
  LENGTH _trt_code $1; /*Temp variable containing TRT_CODE*/
  RETAIN _trt_code '';
  UPDATE adverse (in=a) patdata;
  BY subject;
  IF a;
  IF FIRST.subject THEN _trt_code=trt_code;
  ELSE trt_code=_trt_code;
RUN;


*--------------------------------------;
* 8. Merge with an ARRAY               ;
*--------------------------------------;
%macro numobs(ds,vr);
   %global &vr;
   data _null_;
      if 0 then set &ds nobs=nobs;
      call symput("&vr",trim(left(put(nobs,8.))));
      stop;
   run;
%mend numobs;

proc delete data=alldata0;

%numobs(patdata,xpatdata);
%numobs(adverse,xadverse);

DATA alldata0;
   LENGTH trt_code $1;
   ARRAY f{&xpatdata.,2} $7 _TEMPORARY_;
   DO i=1 TO &xpatdata.;
      SET patdata (RENAME=(trt_code=trt_code_dict));
      f{i,1}=PUT(subject,6.);
      f{i,2}=trt_code_dict;
   END;
   DO i=1 TO &xadverse.;
      SET adverse;
      trt_code='';
      DO j=1 TO &xpatdata.;
         IF subject=f(j,1) THEN DO;
            trt_code=f{j,2};
            OUTPUT;
         END;
         IF ^MISSING(trt_code) THEN LEAVE;
      END;
      IF MISSING(trt_code) THEN OUTPUT;
   END;
   DROP i j trt_code_dict;
RUN;


*--------------------------------------;
* 7. MODIFY Statement                  ;
*--------------------------------------;

proc delete data=alldata0;
DATA adverse alldata0;
  DO p=1 TO totobs;
    _iorc_=0;
    SET patdata point=p nobs=totobs;
    DO WHILE(_iorc_=%sysrc(_sok));
      MODIFY adverse KEY=subject;
      SELECT (_iorc_);
        WHEN (%sysrc(_sok)) DO; /*Match Found*/
          SET patdata POINT=p; OUTPUT alldata0; END;
        WHEN (%sysrc(_dsenom)) _error_=0; /*No Match*/
        OTHERWISE DO; /*A major problem somewhere*/
          PUT 'ERR' 'OR: _iorc_ = ' _iorc_ /
              'program halted.'; _error_ = 0;
          STOP; END;
      END;
    END;
  END;
  STOP;
RUN;


*--------------------------------------;
* 6. Hash Tables.  Will need SAS       ;
*    version 9 or above -- hash tables ;
*    were not available on version 8.x ;
*    or before.                        ;
*--------------------------------------;

proc delete data=alldata0;
DATA alldata0;
  IF _n_=0 THEN SET patdata;
  IF _n_=1 THEN DO;
    DECLARE HASH _h1 (dataset: "PATDATA");
    rc=_h1.definekey("SUBJECT");
    rc=_h1.definedata("TRT_CODE");
    rc=_h1.definedone();
    call missing(SUBJECT,TRT_CODE);
  END;
  SET adverse;
  rc=_h1.find();
  IF rc^=0 THEN trt_code=" ";
  DROP rc;;
RUN;


*--------------------------------------;
* A Break in the Countdown -- CALL     ;
* EXECUTE                              ;
*--------------------------------------;

data _null_;
   set patdata;
   call execute("data _dat0;"||
                "set adverse;"||
                "where subject='"||compress(subject)||"';"||
                "trt_code='"||compress(trt_code)||"';"||
                "proc append base=alldata0 force;"||
                "run;");
run;


*--------------------------------------;
* 5. Point Option                      ;
*--------------------------------------;

proc delete data=alldata0;
DATA alldata0;
  SET adverse;
  DROP _: match; * Drop temporary variables;
  match=0;
  DO i=1 TO xnobs;
    SET patdata (rename=(subject=_subject)) NOBS=xnobs POINT=i;
    IF subject=_subject THEN DO;
      match=1; OUTPUT; END;
  END;
  IF match=0 THEN DO; * Output AE record if no match in CM;
    trt_code=compress(' ');  * CALL MISSING used in presentation -- this routine not available before version 9.x;
    OUTPUT;
  END;
RUN;


*--------------------------------------;
* 4. FORMAT Procedure                  ;
*--------------------------------------;

proc delete data=alldata0;
DATA fmt;
   RETAIN fmtname 'TRT_FMT' type 'C';
   SET patdata;
   RENAME subject=start trt_code=label;

PROC FORMAT CNTLIN=fmt;

DATA alldata0;
   SET adverse;
   ATTRIB trt_code LENGTH=$1
   LABEL='Treatment Code';
   trt_code=PUT(subject,$trt_fmt.);
RUN;


*--------------------------------------;
* 3. KEY= option                       ;
*--------------------------------------;

proc delete data=alldata0;
DATA alldata0;
   SET adverse;
   SET patdata KEY=subject /UNIQUE;
   DO;
      IF _IORC_ THEN DO;
         _ERROR_=0;
         trt_code='';
      END;
   END;
RUN;


*--------------------------------------;
* 2. SQL                               ;
*--------------------------------------;
proc delete data=alldata0;
PROC SQL;
   CREATE TABLE alldata0 AS
      SELECT a.*, b.trt_code
      FROM adverse a LEFT JOIN patdata b
      ON a.subject=b.subject;
   QUIT;
RUN;


*--------------------------------------;
* 1. MERGE Statement                   ;
*--------------------------------------;

proc delete data=alldata0;
DATA alldata0;
   MERGE adverse (in=a) patdata (in=b);
   BY subject;
   IF a;
RUN;


/*EOF*/