SAS问题以基于组删除行

Question

我有2个数据集，

我需要SAS查询

Table 1
ID      Prin    Int     Tot     TranType    TranDt
101     100     15      115     P           1/1/2021
101     100     15      115     P           1/1/2021
102     500     15      515     P           5/1/2021
103     300     15      315     P           6/1/2021
104     250     10      260     P           8/1/2021
105     350     10      360     P           9/1/2021
105     300     10      310     P           10/1/2021

Table 2
ID      Prin    Int     Tot     TranType    TranDt
101     100     15      115     R           1/15/2021
103     300     15      315     R           6/2/2021
105     350     10      360     R           9/5/2021
105     300     10      310     R           10/5/2021

最终输出的

Table 3
ID      Prin    Int     Tot     TranType    TranDt     Cat
101     100     15      115     P           1/1/2021   Rev
101     100     15      115     P           1/1/2021   Good
102     500     15      515     P           5/1/2021   Good
103     300     15      315     P           6/1/2021   Rev
104     250     10      260     P           8/1/2021   Good
105     350     10      360     P           9/1/2021   Rev
105     300     10      310     P           10/1/2021  Rev

帮助，我需要组合表1和表2，以及（ID prin int tot）和trantype = p的组合 匹配表2（id prin int tot）和trantype = r然后 如果应将重复标记为好，则将该行标记为Rev，但第二行。 输出如表3所示。 只要它是＆gt; = table1.trandt，表2的trandt就很好。

感谢此查询的任何帮助。

我尝试分组，但我无法使其起作用。

Answer 1

冗长但产生所需的输出。

proc sql;
    create table stage1 as
        select t1.id,
            t1.prin,
            t1.int,
            t1.tot,
            t1.trantype,
            t1.trandt,
            t2.trantype as trtype
        from table1 t1
            left join
                table2 t2
                on t1.id = t2.id
                and t1.prin = t2.prin
                and t1.int = t2.int
                and t1.tot = t2.tot
    ;
quit;

data stage2;
    set stage1;
    by id prin int tot notsorted;
    if first.tot then group+1;
run;

proc sort data=stage2 out=stage3; by id prin int tot group; quit;

data want;
    length cat $4.;

    do until (last.tot);
        set stage3;
        by id prin int tot group;

        if first.group then
            do;
                if trantype = 'P' and trtype = 'R' then
                    Cat = 'Rev';
                else Cat = 'Good';
            end;

        if last.group and not first.group then
            do;
                if trantype = 'P' and trtype = 'R' then
                    Cat = 'Good';
            end;

        output;
    end;
    drop trtype group;
run;

＆nbsp;＆nbsp;＆nbsp;＆nbsp;＆nbsp; nbsp; nbsp; nbsp; nbsp;＆nbsp;＆nbsp;

Answer 2

proc sort data=a1; by ID Prin Int Tot; run;
proc sort data=a2(rename=(TranType=TranType2 TranDt=TranDt2)); by ID Prin Int 
 Tot; run;

data a12(drop=TranType2 TranDt2 t); merge a1 a2; by ID Prin Int Tot;
 if TranDt<=TranDt2 or TranDt2='';
 if TranType='P' and TranType2='R' then cat='Rev ';
 if first.Tot then t=0; t+1;
 if t>1 or TranDt2='' then cat='Good';
run;