SAS程序员测试（一）

shiyiming

Merge有三种类型：
一对一
一对多
多对多

一对一和一对多情况下，与SQL是相同的。

多对多的情况，如2*3只会有2条match的记录。会发生数据丢失，所以多对多的情况最好是慎用。

shiyiming

............看不懂

shiyiming

data credit;
input Card_Number $ Charge;
char=abs(charge);
datalines;
A123 14.56
A123 15.23
A123 -14.56
A234 11.12
A234 3.87
A234 11.12
A234 -11.12
A234 4.86
run;
proc sort;
by card_number char charge;
run;
data Credit; set credit;
if charge<0 then do;
k=_n_;
delete;
retain k; end;
if _N_=k+1 then delete;
drop char K;
run;

Note: The order of data set is the original order.

shiyiming

本人过去是写sql,转写sas时间不长，下面这个方法主要是用sql来写的，时间紧促，备注就随便写一写，大家请指教：

/*数据输入*/
data tem;
input Card_Number $4. Charge;
cards;
A123 14.56
A123 15.23
A123 -14.56
A234 11.12
A234 11.12
A234 3.87
A234 11.12
A234 -11.12
A234 -11.12
A234 4.86
;run;

/*增加一列序列号，1,2,3,4……*/

data tt;
set tem;
id=_n_;
run;

/*
为了得到这样的分组序号效果
A234 11.12  1
A234 11.12  2
A234 11.12  3
A234 -11.12 1
A234 -11.12 2
………
*/

proc sql;
create table tt1 as
select    a.*,
    (select count(*) from tt where Card_Number=a.Card_Number and Charge=a.Charge and ID<=a.ID) as n
from   tt a        ;
quit;


/*把负数（还款）取出来*/

data t1;
set tt1;
money=abs(Charge);
where Charge<0;
run;

/*做left join增加两列，如果这两列都为0，则就是最终所要的数据*/

proc sql;
create table t2 as
select  a.Card_Number,a.Charge,b.Charge as ref_charge1,c.Charge as ref_charge2
from tt1 a
left join t1 b on a.Charge=b.money and a.Card_Number=b.Card_Number and a.n=b.n and a.charge>=0
left join t1 c on a.Charge=c.Charge and a.Card_Number=c.Card_Number and a.n=c.n and a.charge<0
;
quit;

data t3 ;
set t2;
drop ref_charge1 ref_charge2;
where ref_charge1=. and ref_charge2=.;
run;

shiyiming

N年前的贴子了，这道题目还是挺经典的。不过有个朋友说会重复，其实那段是注释掉的，以下才是我写的算法。你可以试看看，帮我测试测试，呵。

/*在多笔CHARGE对一笔冲销,或多笔CHARGE对多笔冲销，以及一笔CHARGE对多笔冲销的情况下,以下算法均能正确*/
DATA WORK.CHARGE_RETAIN;
SET WORK.CHARGE;
SET WORK.RETURN KEY=CARD_CHARGE;
RETAIN SEQ_LST;
MATCH_IND=0;
IF _IORC_ EQ 0 AND SEQ NE SEQ_LST THEN DO;
SEQ_LST=SEQ;
MATCH_IND=1;
OUTPUT;
END;
IF _IORC_ NE 0 THEN DO;
OUTPUT;
_IORC_=0;
END;
RUN;

DATA WORK.CHARGE_RESULT(DROP=MATCH_IND);
SET WORK.CHARGE_RETAIN(KEEP=CARD_NBR CHARGE_AMT MATCH_IND);
IF MATCH_IND=1 THEN DELETE;
RUN;

shiyiming

I think we need more information to recognize which charge record the return record really refers. So far, it  just refers to the nearest one for the same card number  with a same charge value, which is not always true.

shiyiming

思路：对每组中相同记录分别编号，然后去交集。

[code:33ikojp9]data WORK.SOURCE;
input CARD_NBR $4. AMOUNT;
cards;
A123 14.56
A123 15.23
A123 -14.56
A234 11.12
A234 3.87
A234 11.12
A234 11.12
A234 -11.12
A234 -11.12
A234 4.86
A234 -9.99
A234 -11.13
A123 -14.56
;
run;

data ex1 ex2;
   set source;
   if  amount>=0 then output ex1;
   if  amount <0 then do ;
       amount=-amount;
       output ex2;
           end;
   run;
proc sort data=ex1 out=ex1;by  CARD_NBR  AMOUNT; run;
proc sort data=ex2 out=ex2;by  CARD_NBR  AMOUNT; run;

data ex1;
    set ex1;
        by          CARD_NBR  AMOUNT;
        if (first.amount=1 and last.amount = 1 )  then num=1;  
    else if (first.amount=1 and last.amount ne 1)  then num=1;
    else if (first.amount ne 1 and last.amount ne 1) then num+1;
    else if (first.amount ne 1 and last.amount =1) then num+1;
run;

data ex2;
    set ex2;
    by  CARD_NBR  AMOUNT;
        if (first.amount=1 and last.amount = 1 )  then num=1;  
    else if (first.amount=1 and last.amount ne 1)  then num=1;
    else if (first.amount ne 1 and last.amount ne 1) then num+1;
    else if (first.amount ne 1 and last.amount =1) then num+1;
run;

proc sql;
    select * from ex1
        except
        select * from ex2;
        quit;[/code:33ikojp9]

shiyiming

如果全部用data步的 话。

[code:lwb7etar]
...

data ex3;
   merge ex1(in=in1) ex2 (in=in2);
   if in1 and ^in2 then output;
   by  CARD_NBR  AMOUNT num;
   run;[/code:lwb7etar]

shiyiming

不知道是不是这个意思
data a;
input Card_Number $ Charge;
cards;
A123 14.56
A123 15.23
A123 -14.56
A234 11.12
A234 3.87
A234 11.12
A234 -11.12
A234 4.86
;
run;
proc sort data=a; by card_number; run;

data b;
        set a;
        by card_number;
        abscharge=abs(charge);
run;
proc sort data=b; by card_number abscharge charge ; run;

data c;
        set b;
        by card_number abscharge charge;
        if first.abscharge then charge0=charge;
                else charge0+charge;
        if charge0 > 0 then output;
run;

shiyiming

非常经典的题目。我来一个投机取巧的，大家拿砖头拍....<!-- s:shock: --><img src="{SMILIES_PATH}/icon_eek.gif" alt=":shock:" title="Shocked" /><!-- s:shock: -->
要保证新的id=&quot;ZZZZZZZ&quot;在按id排序后排到最后一行
[code:3lvv2a4x]data a;
input id $ fee;
aa=abs(fee);
cards;
A123 14&#46;56
A123 15&#46;23
A123 -14&#46;56
A234 11&#46;12
A234 3&#46;87
A234 11&#46;12
A234 -11&#46;12
A234 4&#46;86
;
run;
proc sort;
   by id aa;
run;
*要保证生成的id=ZZZZZZZ能在排序后排到最后;
data c;
   input id $;
   cards;
ZZZZZZZ
   ;
run;
*以lag后的id和fee新的id和fee;
data b;
   retain id id_new;
   set a c;
   id_New=lag(id);
   fee_new=lag(fee);
run;
proc sort;
   by id id_new aa;
run;
*indicate1和indicate2为前后两项是否相反的标示;
data b;
   set b;
   by id_new;
   bb=lag(fee_new);if first&#46;id_new then bb=&#46;;
   indicate1=fee_new+bb;
   indicate2=fee+fee_new;
   if indicate1=0 or indicate2=0 then delete;
   if id_new^='';
   keep id_new fee_new;
run;
[/code:3lvv2a4x]
经测试，上面的程序有误...... <!-- s:( --><img src="{SMILIES_PATH}/icon_sad.gif" alt=":(" title="Sad" /><!-- s:( -->
下面的不知道是否正确：
[code:3lvv2a4x]data a;
input id $ fee;
aa=abs(fee);
sign=sign(fee);
cards;
A123 14&#46;56
A123 15&#46;23
A123 -14&#46;56
A234 11&#46;12
A234 3&#46;87
A234 11&#46;12
A234 11&#46;12
A234 11&#46;12
A234 11&#46;12
A234 -11&#46;12
A234 -11&#46;12
A234 -11&#46;12
A234 4&#46;86
A234 4&#46;86
;
run;
proc sort;
   by id aa sign;
run;
data d;
   set a;
   by id aa sign;
   retain x;
   *计数出现负的个数;
   if first&#46;aa then do;
      if sign=-1 then x=1;else x=0;
   end;
   else do;
      if sign=-1 then x+1;
   end;
   *出现正的非第一行逐行减1，可能会出现负的情况;
   *if sign=1 and ^(first&#46;sign) then x=x-1;
   if ^(first&#46;sign) then  do;
      if sign=1 then x=x-1;
   end;
   *删除标示&gt;0的行;
   *if x&gt;0 then delete;
   *keep id fee;
run;
[/code:3lvv2a4x]