SAS中文论坛

标题: 求助:记录抵消 [打印本页]

作者: shiyiming 时间: 2004-4-8 18:29
标题: 求助:记录抵消
我遇到了xic帖子上相似的问题。 <a class="postlink" href="http://shiyiming.51.net/forum/viewtopic.php?t=488">http://shiyiming.51.net/forum/viewtopic.php?t=488</a>
数据库中，有费用，以及人员信息等信息。但包括收费退费。目的要把收费和退费的记录抵消，抵消后的数据库要保持原始的数据结构，即原来的字段一个也不能少。该怎么办？
[code:50189]data aaa;
input YB_ZYID $ YB_NAME $ YB_BXID $ YB_YBID $ YB_CYDAT $ YB_ZYDNU YB_ZYJGB $ YB_KSID $ YB_KSNA $ YB_GRXZ $ YB_TOTFY YB_GRXJD YB_GRXJQ YB_TCZF YB_GRZFL YB_GRZF1 YB_FJBX YB_FY01 YB_FY02 YB_FY03 YB_FY04 YB_FY05 YB_FY06 YB_FY07 YB_FY08 YB_FY09 YB_FY10 YB_FY11 YB_FY12 YB_FY13 YB_FY14 YB_FY15 YB_ZDICD $ YB_ZDNAM $ YB_BJQK $ YB_DAA $ YYID $ YB_SN $ YYJB $;
cards;
0000308596 张继广 310105192205100419 1200178856 20020108 -191 1 1140 肾病科 2 -256985.81 -7.6 -44537.16 -51520 -692.4 0 -160228.65 -5348 -1910 -42437.7 -1892 -973.25 -263 -16967 -672 0 -14180 -3282 -160435.75 -3637.5 -3853.61 -1134 05-250 糖尿病 0 2002.01 425005069 YD060213.DBF0000000246 03
0000308596 张继广 310105192205100419 1200178856 20020108 191 1 1140 肾病科 2 257898.21 7.6 44719.64 51520 692.4 0 160958.57 5348 1910 42450.45 1907 978.66 263 16967 756 0 14600 3282 160795.99 3637.5 3853.61 1149 05-250 糖尿病 0 2002.01 425005069 YD060213.DBF0000000247 03
0000308596 张继广 310105192205100419 1200178856 20020108 191 1 1140 肾病科 2 256985.81 7.6 44537.16 51520 692.4 0 160228.65 5348 1910 42437.7 1892 973.25 263 16967 672 0 14180 3282 160435.75 3637.5 3853.61 1134 05-250 糖尿病 0 2002.01 425005069 YD060213.DBF0000000248 03
0000372954 胡学政 310107193306172814 1600274890 20020109 -193.5 1 1300 肺科 2 -210214.39 -657.14 -35182.87 -51520 -42.86 0 -122811.52 -5306 -1895 -83433.25 -1895 -198.56 -420 -17265 -1447 0 -2128 -8467.2 -86106.38 0 0 -1653 12-491 慢性支气管炎继发感? 0 2002.01 425005069 YD060213.DBF0000000301 03
0000372954 胡学政 310107193306172814 1600274890 20020109 193.5 1 1300 肺科 2 210214.39 657.14 35182.87 51520 42.86 0 122811.52 5306 1895 83433.25 1895 198.56 420 17265 1447 0 2128 8467.2 86106.38 0 0 1653 12-491 慢性支气管炎继发感? 0 2002.01 425005069 YD060213.DBF0000000302 03
0000372954 胡学政 310107193306172814 1600274890 20020109 193.5 1 1300 肺科 2 212931.33 657.14 35726.26 51520 42.86 0 124985.07 5418 1935 84575.95 2220 305.07 450 17651 1447 0 2128 8563.2 86537.11 0 0 1701 12-491 慢性支气管炎继发感? 0 2002.01 425005069 YD060213.DBF0000000303 03
0000372954 胡学政 310107193306172814 1600274890 20020109 -193.5 1 1300 肺科 2 -210214.39 -657.14 -35182.87 -51520 -42.86 0 -122811.52 -5306 -1895 -83433.25 -1895 -198.56 -420 -17265 -1447 0 -2128 -8467.2 -86106.38 0 0 -1653 12-491 慢性支气管炎继发感? 0 2002.01 425005069 YD060213.DBF0000000304 03
0000372954 胡学政 310107193306172814 1600274890 20020109 -193.5 1 1300 肺科# 2 -210214.39 -657.14 -35182.87 -51520 -42.86 0 -122811.52 -5306 -1895 -83433.25 -1895 -198.56 -420 -17265 -1447 0 -2128 -8467.2 -86106.38 0 0 -1653 12-491 慢性支气管炎继发感? 0 2002.01 425005069 YD060213.DBF0000000305 03
0000372954 胡学政 310107193306172814 1600274890 20020109 193.5 1 1300 肺科 2 210214.39 657.14 35182.87 51520 42.86 0 122811.52 5306 1895 83433.25 1895 198.56 420 17265 1447 0 2128 8467.2 86106.38 0 0 1653 12-491 慢性支气管炎继发感? 0 2002.01 425005069 YD060213.DBF0000000306 03
0000372954 胡学政 310107193306172814 1600274890 20020109 193.5 1 1300 肺科 2 210214.39 657.14 35182.87 51520 42.86 0 122811.52 5306 1895 83433.25 1895 198.56 420 17265 1447 0 2128 8467.2 86106.38 0 0 1653 12-491 慢性支气管炎继发感? 0 2002.01 425005069 YD060213.DBF0000000307 03
;
run;[/code:50189]

作者: shiyiming 时间: 2004-4-9 22:43
I guess you have not given a complete definition of your question. If you would like to delete charge and refund pairs, you have to tell which variable represents the charge/refund, and within a pair, all the other information should be exactly same. It seems to me that the first and the third record consist of a pair, but there are some other values different. If this pair is eliminated, how can you retain the information difference. You have to give a list of variables which need to be kept, it is the starting point to talk about this problem.

作者: shiyiming 时间: 2004-4-10 00:32
i mean all the variables should be reserved or kept as many as possible. the variable with different value is because a pationt was charged in a ward but when he was refunded , he is in other ward. i don't know whether i explain my problem clearly. but i really hope u can help me;
3x

作者: shiyiming 时间: 2004-4-10 03:55
I can not test your data set, since my system does not support Chinese font.  But, I think the following program will do whatever you want.  Please pay attention that I do not care which two records form a pair, you can do some refinement.  When you post this question, at least you have to tell us which variable represent charge and refund, I just guessed it here.


data aaa bbb(keep=yb_name yb_zydnu absamt index);
set aaa;
by yb_name;
if first.yb_name then index=0;
index+1;
output aaa;
absamt=abs(yb_zydnu);
output bbb;
run;

proc sort data=bbb;
by yb_name absamt;
run;

data bbb1 bbb2;
retain i j;
set bbb;
by yb_name absamt;
if first.absamt then do;
i=0; j=0;
end;
if yb_zydnu>=0 then do;
  i+1;
  output bbb1;
end;
else do;
  j+1;
  output bbb2;
end;
run;

proc sql;
create table keep as
select a.yb_name as ayb_name, b.yb_name as byb_name,
   a.index as aindex, b.index as bindex,
   a.i, b.j
from bbb1 a
full join bbb2 b
on a.yb_name=b.yb_name and
a.i    =b.j;
quit;

data keep;
set keep;
if i=j then delete;
index=max(aindex,bindex);
if ayb_name ne ' ' then yb_name=ayb_name;
else yb_name=byb_name;
run;

proc sql;
create table aaa(drop=absamt index) as
select a.*
from aaa a, keep b
where a.yb_name=b.yb_name and
   a.index  =b.index
order by a.yb_name, a.index;
quit;

作者: shiyiming 时间: 2004-4-10 17:35
I thank that you can add and column name that can identify the record if it belong to the origin data.and then use the sql procedural to calculate The column that are need to be calculated ,in this procedural ,you should create a new table having the same column name but the identifiable column having the diferent value to identify the record if it come from the origin data or not.finaly, unite the two table as one.

作者: shiyiming 时间: 2004-4-10 18:02
Here is the example:
data taaa;
      set aaa;
      Frm='origin'
run;
PROC SQL;
create table ta as
  SELECT sum(yb_zydnu) as yb_zydnu,sum(yb_totfy) as yb_totfy ,....., sum(yb_fy15) as yb_fy15 FROM taaa  GROUP BY YB_ZYID;
data faaa;
   set taaa ta;
run;

欢迎光临 SAS中文论坛 (http://mysas.net/forum/)