问将行对转换为单行

EN

/* Create sample data */
data have;
    input Prefix $ ID Previous_Prefix $ Previous_ID Vendor_1 $ Vendor_2 $;
    cards;
A 1  .  .  JAC BOA
B 2  C  99 LCH GS
B 3  C  99 LCH JPM
E 5  F  52 LOK U
E 5  F  52 LOK M
    ;;;
run;

/* Assign new values for Prefix & ID */
data want;
    set have;
    if Previous_Prefix ne '' then do;
        Prefix = Previous_Prefix;
        Previous_Prefix = '';
    end;
    if Previous_ID ne . then do;
        ID = Previous_ID;
        Previous_ID = .;
    end;
run;

/* Merge Vendor_1 & Vender2 into one column */
data want2;
    set want;
    if first.ID then n = 0;
    n + 1;
    by ID notsorted;
run;

proc transpose data=want2 out=want3(drop=n _NAME_ rename=(COL1=Vendor));
    by Prefix ID n Previous_Prefix Previous_ID;
    var Vendor_1 Vendor_2;
run;

/* Delete common Vendor_ */
proc sort data=want3;by Prefix ID Previous_Prefix Previous_ID Vendor;run;

proc summary data=want3;
    output out=want4(where=(_freq_ = 1));
    by Prefix ID Previous_Prefix Previous_ID Vendor notsorted;
run;

/* Transpose data from long back to wide */
proc transpose data=want4 out=want5(drop=_:) prefix=Vendor_;
    var Vendor;
    by Prefix ID Previous_Prefix Previous_ID notsorted;
run;

DATA WANT;
SET HAVE;
  PREFIX_NEW =COALESCEC(PREVIOUS_PREFIX,PREFIX);
  ID_NEW     =COALESCEC(PREVIOUS_ID    ,ID);
IF NOT MISSING(VENDOR_1) THEN VENDOR=VENDOR_1; 
OUTPUT;
IF NOT MISSING(VENDOR_2) THEN VENDOR=VENDOR_2; 
OUTPUT;
RUN;

PROC SQL;
SELECT PREFIX_NEW
     , ID_NEW
     , MIN(VENDOR) AS VENDOR_1
     , MAX(VENDOR) AS VENDOR_2
FROM ( SELECT PREFIX_NEW
            , ID_NEW
            , VENDOR
            , COUNT(*) AS COUNTER
         FROM WANT
         GROUP BY 1,2,3
         HAVING COUNTER=1
      )
;
QUIT;