b*****r
发帖数: 359 | 1 有两个表格，如图 table1 和 table2。
要执行的操作：
if table1.x = table2.x AND table1.y = table2.y and table1.z = table2.z then
num_new = num + A (A is a constant)
else
num_new = num + B (B is another constant)
也就是说，表格1里面有5万条数据，表格2里面有100条数据。
只有当表格1里面的 x,y 和 z 同时等于 表格2里面的x y z
的时候， num_new = num + A; 否则， num_new = num+B.
最后得到的新表格的行数和表格1 是一样多的，也是5万条。
怎样实现呀？
使用 data step 语句 还是 proc SQL ?
谢谢 |
s*z
发帖数: 132 | 2 * cartesian product join of all rows;
proc sql;
create table test as
select a.x as x1,
a.y as y1,
a.z as z1,
b.x as x2,
b.y as y2,
b.z as z2,
case when a.x = b.x and a.y = b.y and a.z = b.z then
num + A
else num + B
end as num_new
from table1 as a, table2 as b
;
quit; |
G**7
发帖数: 391 | 3 1st, y hunch told me that proc sql might be more efficient.
2nd, try "do while" or "Do... if.." statement. My understanding is that
you want to compare each value in Table X with each value in Table Y.
|
a****g
发帖数: 8131 | 4 you can also use data step
if *** then do;
else ***;
then
【在 b*****r 的大作中提到】
: 有两个表格，如图 table1 和 table2。
: 要执行的操作：
: if table1.x = table2.x AND table1.y = table2.y and table1.z = table2.z then
: num_new = num + A (A is a constant)
: else
: num_new = num + B (B is another constant)
: 也就是说，表格1里面有5万条数据，表格2里面有100条数据。
: 只有当表格1里面的 x,y 和 z 同时等于 表格2里面的x y z
: 的时候， num_new = num + A; 否则， num_new = num+B.
: 最后得到的新表格的行数和表格1 是一样多的，也是5万条。
|
b*****r
发帖数: 359 | 5 Thank you very much!
But do NOT need to join of all rows!
the new table "test" should have the same number of rows as table 1;
【在 s*z 的大作中提到】
: * cartesian product join of all rows;
: proc sql;
: create table test as
: select a.x as x1,
: a.y as y1,
: a.z as z1,
: b.x as x2,
: b.y as y2,
: b.z as z2,
: case when a.x = b.x and a.y = b.y and a.z = b.z then
|
b*****r
发帖数: 359 | 6 like this?
data new_table; set table1 table2;
if table1.x = table2.x AND table1.y = table2.y and table1.z = table2.z then
num_new = num + A;
else
num_new = num + B;
run;
这个出错：
ERROR: DATA STEP Component Object failure. Aborted during the COMPILATION phase.
ERROR 557-185: Variable table1 is not an object.
【在 a****g 的大作中提到】
: you can also use data step
: if *** then do;
: else ***;
:
: then
|
s*z
发帖数: 132 | 7
then
left join
on a.x = b.x and a.y = b.y and a.z = b.z
【在 b*****r 的大作中提到】
: Thank you very much!
: But do NOT need to join of all rows!
: the new table "test" should have the same number of rows as table 1;
|
a****g
发帖数: 8131 | 8 add 'do;' after 'then'
then
phase.
【在 b*****r 的大作中提到】
: like this?
: data new_table; set table1 table2;
: if table1.x = table2.x AND table1.y = table2.y and table1.z = table2.z then
: num_new = num + A;
: else
: num_new = num + B;
: run;
: 这个出错：
: ERROR: DATA STEP Component Object failure. Aborted during the COMPILATION phase.
: ERROR 557-185: Variable table1 is not an object.
|
b*****r
发帖数: 359 | 9 刚刚试过，出现同样的错误。
在then后面，只有一个语句，所以加和不加 do；
都是一样的。如果在then后面，有多条语句，就应该加 do； ... end;
要是我错了，请指出。
【在 a****g 的大作中提到】
: add 'do;' after 'then'
:
: then
: phase.
|
s*z
发帖数: 132 | 10 You can not use set to merge columnwise in data step. Set is used to do
rowwise union.
search for 'sas data step merge', and try something like this:
proc sort data = table1;
by x y z;
proc sort data = table2;
by x y z;
data new_table;
merge table1 table2;
by x y z;
if table1.x = table2.x AND table1.y = table2.y and table1.z = table2.z
then num_new = num + A;
else num_new = num + B;
run; |
a****g
发帖数: 8131 | 11 我看错了
我以为是一个dataset里面
两个dataset你用sql就很好了
【在 b*****r 的大作中提到】
: Thank you very much!
: But do NOT need to join of all rows!
: the new table "test" should have the same number of rows as table 1;
|
a****g
发帖数: 8131 | 12 你这个set statement是combine data verticallly
你这种情况不能这样处理
【在 b*****r 的大作中提到】
: 刚刚试过，出现同样的错误。
: 在then后面，只有一个语句，所以加和不加 do；
: 都是一样的。如果在then后面，有多条语句，就应该加 do； ... end;
: 要是我错了，请指出。
|
b*****r
发帖数: 359 | 13 this works!
Thank you very much!
【在 s*z 的大作中提到】
: You can not use set to merge columnwise in data step. Set is used to do
: rowwise union.
: search for 'sas data step merge', and try something like this:
: proc sort data = table1;
: by x y z;
: proc sort data = table2;
: by x y z;
: data new_table;
: merge table1 table2;
: by x y z;
|
b*****r
发帖数: 359 | 14 问题解决了，
谢谢各位的帮助！
【在 a****g 的大作中提到】
: 你这个set statement是combine data verticallly
: 你这种情况不能这样处理
|
