SAS程序员测试（一）

shiyiming

看不懂~~~~~~~~~~~~~~~~~

shiyiming

这是我的程序，供大家参考:请多多指教。
程序输出结果保存在out数据库中
data credit_card;
input card_number $ charge;
cards;
A123 14.62
A123 -14.62
A123 56.23
A124 45.2
A124 23.8
A124 -46.2
A125 56.2
A125 -28.6
;
run;
proc sql ;
create table out as
select card_number, sum(charge)
from credit_card
group by card_number;
run;

shiyiming

data Sasuser.test;
imput Card_Number $1-4 Charge 6-12;
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 sql;

create table temp1 as
select * from Sasuser.test where Charge>0 ;
create table temp2 as
select Card_Number,abs(charge) from Sasuser.test where Charge<0;
select * from temp1 except all (select * from temp2);

run;

shiyiming

I would like to follow the exercise.  I am a neophyte although I have some programming experience in other programming language.
here is my solution, I hope I understand the issue correctly.

data test2;
input id$ amount;
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
;

data pos neg;
set test2;
select;
when (amount>0) output pos;
when (amount<0) do;
        amount=-amount;
        n=1;
        output neg ;
end;
run;
proc sort data=pos out=pos;
by id$ amount;

proc sort data=neg out=neg;
by id$ amount;

data merge;
merge pos neg;
by id$ amount;

data merge;
if n=1 then delete;
drop n;
run;

shiyiming

data charge;
input Card_Number $ Charge;
absCharge=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 data=Charge;
  by Card_number absCharge;
run;

proc means data=charge noprint;
  by Card_Number absCharge;
  output out=out sum=sum;
run;

data result;
  set out;
  Charge = sign(sum)*absCharge;
  n = abs(sum/abscharge);
  do i=1 to n;
    output;
  end;
  keep Card_Number Charge absCharge;
run;
<!-- s:D --><img src="{SMILIES_PATH}/icon_biggrin.gif" alt=":D" title="Very Happy" /><!-- s:D -->

shiyiming

[quote=&quot;xic&quot;:c835f]Here is a small test for your SAS programming skills, which was posted by some one in SAS User Group several years ago.  I did it in my way, but I would like to share it with you all.

There is a Credit Card charging record data set, each line is either a charge or a return.  Charge is a positive number, and return is negative.  Assume that each item will be charged once, so if there is a return, it will be a full refund.  A sample data set looks like the following:

Card_Number               Charge <!-- s:twisted: --><img src="{SMILIES_PATH}/icon_twisted.gif" alt=":twisted:" title="Twisted Evil" /><!-- s:twisted: -->
A123                   14.56
A123                   15.23
A123                   -14.56
A234                   11.12
A234                     3.87
A234                   11.12
A234                   -11.12
A234                     4.86

Since the charge/return pair does not generate revenue, it should be deleted.  So, the cleaned data set should look like the following:

Card_Number              Charge
A123                   15.23
A234                   11.12
A234                     3.87
A234                     4.86

Please write a SAS program to accomplish this task.

You can give yourself a grade based on the following criteria:

60 - your program will solve this example correctly;
70 - your program will not only solve this example correctly, but also  works for extended records following the same logic;
80 - besides meeting the criteria for 70, your program has a clear logical structure and other programmer is able to understand it without raising questions.
90 - clear cut, simple structure, efficient
100 - you can think about it.

If you have interests to do it and have questions about it, spell out.  Good Luck.[/quote:c835f]

shiyiming

我的Email：richard51521@yahoo.com.cn，欢迎交流。
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
A123 -14.56
;
run;
DATA WORK.CHARGE(RENAME=(AMOUNT=CHARGE_AMT)) WORK.RETURN(RENAME=(AMOUNT=RETURN_AMT));
SET WORK.SOURCE;
IF NOT MISSING(AMOUNT) THEN DO;
        IF AMOUNT&lt;0 THEN DO;
                CHARGE=-AMOUNT;
                OUTPUT WORK.RETURN;
        END;
        IF AMOUNT&gt;=0 THEN DO;
                CHARGE=AMOUNT;
                OUTPUT WORK.CHARGE;
        END;
END;
RUN;

PROC SORT DATA=WORK.CHARGE;
BY CARD_NBR CHARGE;
RUN;
PROC SORT DATA=WORK.RETURN;
BY CARD_NBR CHARGE;
RUN;
DATA WORK.RETURN;
SET WORK.RETURN;
SEQ=_N_;
RUN;
/*因为存在相同卡号与金额的情况,会导致一笔return冲掉多笔charge*/
/*
DATA WORK.CHARGE_AGAINST WORK.CHARGE_RETAIN WORK.RETURN_RETAIN;
MERGE WORK.CHARGE(IN=E) WORK.RETURN(IN=F);
BY CARD_NBR CHARGE;
IF E AND F THEN OUTPUT WORK.CHARGE_AGAINST;
IF E AND ^F THEN OUTPUT WORK.CHARGE_RETAIN;
IF ^E AND F THEN OUTPUT WORK.RETURN_RETAIN;
RUN;
*/
/*将CHARGE创建索引*/
PROC DATASETS LIB=WORK NOLIST;
MODIFY RETURN;
        INDEX DELETE CARD_CHARGE;
        INDEX CREATE CARD_CHARGE=(CARD_NBR CHARGE);
RUN;
/*在多笔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

学SAS不久，也来试试！
我的程序如下：
data tem1 tem2;
input card_number $ charge;
abs=abs(charge);
if charge&gt;0 then output tem1;
else output tem2;
cards;
A123 14.56
A123 15.23
A123 -14.56
A123 14.56
A123 15.23
A123 -14.56
A234 11.12
A234 3.87
A234 11.12
A234 -11.12
A234 4.86
A234 11.12
A234 3.87
A234 11.12
A234 11.12
A234 -11.12
A234 4.86
A234 -11.12
run;
proc sort  data=tem1;by card_number abs;run;
proc sort  data=tem2;by card_number abs;run;
data tem(drop=abs);
merge tem1 tem2;
by card_number abs;
if charge&lt;0 then delete;
run;

shiyiming

楼上的兄弟，我不是才说过，用Merge的办法会导致一笔冲销掉多笔吗？？

shiyiming

楼上的这位，我试过几个，用merge是可以的阿。SAS语言跟别的语言不同，merge合并的时候也是一对一的，所以不用担心你说的那个问题吧。不知道我说的对不对？
我刚学SAS不久，很多地方都不懂，希望能向你请教，我的QQ：117605981