SAS程序员测试（一）

shiyiming

good

shiyiming

[quote="小猪":ea894]不好意思，我刚开始学SAS，也来试试。请大侠们多多指教~~
我的思路是在不改变原来顺序的前提下，只用data step 语句，将每一次的return对冲离这个最近的charge，最后得到一个时间序列。

data charge(drop=tt);
set charge;
retain time 0 tt 'xxxx';
if tt=Card_Number then do;
time=time+1;
tt=Card_Number;
end;
else do;
time=1;
tt=Card_Number;
end;
run;

data result;
set charge;
where charge>0;
run;
data tem2;
set charge;
where charge<0;
retain nn 0;
nn=nn+1;
run;
[/quote:ea894]

Thank you for your interests on this homework assignment, I think you did pretty good.  Before I go into your macro for detail, I would like to make some comments on the first part of your program.  I did a small program which will do almost exactly same thing as you did.

data charge;
set charge;
by card_number;
if first.card_number then time=0;
time+1;
run;

data result tem2;
set charge;
if charge>0 then output result;
else if charge<0 then output tem2;
run;

I think it is easier to understand, am I correct?  Surely, I assume that the original data was sorted by card_number, and I did not create nn in tem2.  In fact, the first assumption is also implied in your program, and I will see if the second one is necessary.  I will have a follow up later, have a good weekend.

shiyiming

我也来试一下，但这个小程序得到的结果没有完全按照原题要求的顺序，还望各位高手多多指教


data temp;
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 11.12
A234 4.86
A234 -11.12
A234 6.8
A234 -6.8

;
run;


data temp1;
  set temp;
  if charge >= 0 then delete;
  return = charge;
  drop charge;
run;

data temp;
  set temp;
  if  charge < 0 then delete;
run;

data test;
  merge temp temp1;
  by card_number;
run;

proc sort data= test;
  by card_number charge;
run;

data test;
  set test;
  lagcharge= lag(charge);
  if charge ne lagcharge  then
   do;
    if sum(charge,return) = 0 then delete;
   end;
  drop return lagcharge;
run;

proc sql;
  select distinct card_number,charge
  from test
  order by card_number;
run;


得到的结果

Card_Number    Charge
---------------------
A123            15.23
A234             3.87
A234             4.86
A234            11.12

shiyiming

Just provide a simple solution from an accounting viewpoint, say, revenue = charge + credit for each unique transaction item.  <!-- s:lol: --><img src="{SMILIES_PATH}/icon_lol.gif" alt=":lol:" title="Laughing" /><!-- s:lol: -->  <!-- s:lol: --><img src="{SMILIES_PATH}/icon_lol.gif" alt=":lol:" title="Laughing" /><!-- s:lol: -->  <!-- s:lol: --><img src="{SMILIES_PATH}/icon_lol.gif" alt=":lol:" title="Laughing" /><!-- s:lol: -->  <!-- s:lol: --><img src="{SMILIES_PATH}/icon_lol.gif" alt=":lol:" title="Laughing" /><!-- s:lol: -->

data tem;
input Card_Number $4. Charge;
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 4.86
A234 -11.12
A235 10.87
A235 -10.87
;
run;

data tem;
set tem;
chg_c = put(charge, 8.2);
run;

proc summary data=tem nway;
where charge &lt; 0;
class card_number chg_c;
var charge;
output out=credit(drop = _type_  rename=(_freq_ = ncredit charge=credit)) sum=;
run;

data credit;
set credit;
chg_c = compress(compress(chg_c, '-'), ' ');
run;

proc summary data=tem nway;
where charge &gt;=0;
class card_number chg_c;
var charge;
output out=result(drop= _type_ rename=(_freq_ = ncharge))sum=;
run;

data result;
set result;
chg_c = compress(chg_c, ' ');
run;

proc sort data=credit; by card_number chg_c; run;
proc sort data=result; by card_number chg_c; run;

data result(keep=card_number charge);
merge result credit;
by card_number chg_c;
charge = sum(charge, credit);
if charge ne 0;
run;

proc print data=result noobs; run;

-------------------------------------------------

The SAS System
23:55 Monday, April 26, 2004   1

Card_
Number    Charge

A123      15.23
A234      11.12
A234       3.87
A234       4.86

shiyiming

这是我的程序，它基于一个假设：先消费后还账。
[color=blue:eda74]data a;
  input CardN $ Charge;
  abs=abs(charge);
  if charge&lt;0 then pos=0;
  else pos=1;
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;
  by cardn abs pos;
data c;
  set a;
  retain rep del 0;
  if first.cardn then
     del=0;
  if pos=0 then do;
     del+1;
     end;
  else if del&gt;0 then do;
     del+(-1);
     end;
  else  output;
  run;
proc print;
run;[/color:eda74]

提交运行结果如下：
[color=darkred:eda74]Obs  CardN    Charge
1     A123     15.23
2     A123     15.23
3     A234      3.87
4     A234      3.87
5     A234      4.86
6     A234      4.86
7     A234     11.12
8     A234     11.12 [/color:eda74]

不过，没有用其它数据测试。

shiyiming

先把相同的ID的数据TRANSPOSE成同一行，处理起来比较容易。
[code:10dc9]proc print data=card;
run;
proc transpose data=card prefix=charge out=tpcard&#40;drop=_name_&#41;;
var charge ;
by Card_Number;
run;
data tempcard&#40;keep=card_number charge&#41;;
set tpcard;
array card&#91;*&#93; charge1-charge5;
do i= dim&#40;card&#41; to 1 by -1 ;
   if card&#91;i&#93;=&#46; then continue;
  do j= i-1 to 1 by -1;
if abs&#40;card&#91;i&#93;&#41;=abs&#40;card&#40;j&#41;&#41; and  card&#91;i&#93; ne card&#91;j&#93; then
do;
card&#91;i&#93;=&#46;;
card&#91;j&#93;=&#46;;
leave;
end;
end;
end;
do k=1 to dim&#40;card&#41; ;
if card&#91;k&#93; ne &#46; then
  do;
  charge=card&#91;k&#93;;
  output ;
  end;
end;
run;
proc print data=tempcard;
run;[/code:10dc9]

shiyiming

data charge;
input Card_Number $4. Charge;
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 4.86
A234 -11.12
;
run;
data charge_1;
do n=obs to 1 by -1;
set charge point=n nobs=obs ;
output;
end;
stop;
run;
data charge_1;
set charge_1;
s+1;
run;
data charge_2;
retain tem 100;
set charge_1;
if charge&lt;0 then  tem=charge;
if charge&gt;0 &amp; charge=-1*tem then do;
s=0;
tem=100;
end;
run;
data charge_3;
set charge_2;
where s ^=0 &amp; charge&gt;0;
drop tem s;
run;
data result;
do n=obs to 1 by -1;
set charge_3 point=n nobs=obs ;
output;
end;
stop;
run;

shiyiming

data charge return(drop=charge);
input Card_Number $ Charge;
if charge&gt;=0 then do;
absCharge=charge;
output charge;
end;
else do;
absCharge=abs(charge);
return=charge;
output return;
end;
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=charge;
by Card_Number absCharge;
run;
proc sort data=return;
by Card_Number absCharge;
run;
data revenue(drop=absCharge return);
merge charge return;
by Card_Number absCharge;
if charge^=. and return^=. then delete;
run;

shiyiming

data test;
        input card_number $ charge;
        datalines;
        A123 14.56
        A123 15.23
        A123 -14.56
        A123 -8.0
        A234 11.12
        A234 3.87
        A234 11.12
        A234 -11.12
        A234 4.86
run;
data test2 test3; set test;
        if charge &lt; 0 then output test2;
        else output test3;
run;

data test2; set test2; charge = abs(charge); run;
proc sort data=test2; by card_number charge; run;
data test2; set test2;
        by card_number charge;
        retain ct 0;
        if first.card_number or first.charge then ct = 0;
        ct+1;
        run;
proc sort data=test3; by card_number charge; run;
data test3; set test3;
        by card_number charge;
        retain ct 0;
        if first.card_number or first.charge then ct = 0;
        ct+1;
        run;
proc sort data=test2; by card_number charge ct; run;
proc sort data=test3; by card_number charge ct; run;
data test ot; merge test3(in=in1) test2(in=in2);
        by card_number charge ct;
        if in1 and not in2 then output test;
        else if not in1 and in2 then output ot;
        run;
data test; set test ot(in=a);
        drop ct;
        if a then charge = -1*charge;
        run;
proc print data=test; run;

shiyiming

data source;
input Card_Number $4. Charge;
cards;
A123 14.56
A123 15.23
A123 -14.56
A135 20.00
A234 11.12
A234 3.87
A234 11.12
A234 -11.12
A234 11.12
A234 4.86
A234 -11.12
;
run;
data Charge Payment;
set source;
if Charge&gt;0 then output Charge; else if Charge&lt;0 then output Payment;
run;
proc sql;
create table Sum_Charge as select distinct Card_Number, Charge, count(*) as Cnt_Charge from Charge group by Card_Number, Charge;
create table Sum_Payment as select distinct Card_Number, Charge as Payment, count(*) as Cnt_Payment from Payment group by Card_Number, Charge;
quit;
proc print data=Sum_Charge;run;
data rst(keep=Card_Number Charge);
merge Sum_Charge Sum_Payment;
by Card_Number;
if sum(Charge,Payment) then output;
else do;
        Dif=Cnt_Charge-Cnt_Payment;
        if Dif&gt;0 then do i=1 to Dif; output; end;
end;
run;
proc print;run;