通过使用SQL SQL中的语句合并表(merging tables by using PROC SQL where in statement in SAS)

通过使用SQL SQL中的语句合并表(merging tables by using PROC SQL where in statement in SAS)

大家。 我目前正在使用SAS。 这是我的情况。

我有一个名为'a'的表格，它看起来像：

id_c id_t 5 3 2 9 15 1 65 43 ... ...

这个表格只有两个不同于其他表格的ID。 这5＆3，2＆9，...是成对的。

我想要做的是我要取出每个id_t和id_c，以便我可以将其他变量分别合并到其他表中。 为此，首先我决定取出id_t并合并其他变量。 以下代码有效：

proc sql; create table t1 as select * from other_var_table_1 where id_t in (select id_t from a); quit;

结果是：

id_t var1 var2 var3 3 9 1 43

这里，id_t的顺序与表'a'中id_t的顺序完全相同。

但是，当我通过使用id_c来做同样的事情时，订单就搞砸了。 我想要：

id_c var_x var_y var_z 5 2 15 65

但是当我运行相同的代码时，结果变成：

proc sql; create table t2 as select * from other_var_table_2 where id_c in (select id_c from a); quit; id_c var_x var_y var_z 65 15 5 2

这样做的目的是再次合并两个表格，包括所有变量，例如：

data final; set t2; set t1; run; id_c var_x var_y var_z id_t var1 var2 var3 5 3 2 9 15 1 65 43 ... ...

如果有人能帮助我，我将不胜感激。

谢谢，

everyone. I'm currently using SAS. Here's my situation.

I have a table called 'a', which looks like:

id_c id_t 5 3 2 9 15 1 65 43 ... ...

This,a, table has only two different ids from other tables. These 5&3, 2&9,... are pairs.

What I want to do is that I want to take out each id_t and id_c so that I can merge other variables in other tables, respectively. To do so, first I decide to take out id_t and merge other variables. This following code works:

proc sql; create table t1 as select * from other_var_table_1 where id_t in (select id_t from a); quit;

The result is:

id_t var1 var2 var3 3 9 1 43

Here, the order of id_t is exactly the same as the order of id_t in table 'a'.

However, when I do the same thing by using id_c, the order is messed up. I want:

id_c var_x var_y var_z 5 2 15 65

but when I run the same code, the result becomes such as:

proc sql; create table t2 as select * from other_var_table_2 where id_c in (select id_c from a); quit; id_c var_x var_y var_z 65 15 5 2

The purpose of doing this is to merge the two tables again including all the variables such as:

data final; set t2; set t1; run; id_c var_x var_y var_z id_t var1 var2 var3 5 3 2 9 15 1 65 43 ... ...

It would be greatly appreciated if someone could help me out.

Thank you,

最满意答案

/****CREATING DATASETS****/ data a; input int_c int_t; cards; 5 3 2 9 15 1 65 43 ; run; /*Create a **seq** number so that your order does not change*/ data a; set a; seq = _n_; run; data other_var_table_1; input int_t var1 var2 var3; cards; 3 12 43 76 1 10 20 30 ; run; data other_var_table_2; input int_c var_x var_y var_z; cards; 2 100 200 300 5 1 2 3 65 10 20 30 ; run; /****JOINING THE REQUIRED COLUMNS****/ proc sql; create table final as select r.*,p.var1,p.var2,p.var3,q.var_x,q.var_y,q.var_z from a r left join other_var_table_1 p on r.int_t = p.int_t left join other_var_table_2 q on r.int_c = q.int_c order by r.seq; run;

如果需要，您可以在结果之后放置列序列 。 如果您有任何疑问，请告诉我。

我的输出：

int_c |int_t |SEQ |var1 |var2 |var3 |var_x |var_y |var_z 5 |3 |1 |12 |43 |76 |1 |2 |3 2 |9 |2 |. |. |. |100 |200 |300 15 |1 |3 |10 |20 |30 |. |. |. 65 |43 |4 |. |. |. |10 |20 |30 /****CREATING DATASETS****/ data a; input int_c int_t; cards; 5 3 2 9 15 1 65 43 ; run; /*Create a **seq** number so that your order does not change*/ data a; set a; seq = _n_; run; data other_var_table_1; input int_t var1 var2 var3; cards; 3 12 43 76 1 10 20 30 ; run; data other_var_table_2; input int_c var_x var_y var_z; cards; 2 100 200 300 5 1 2 3 65 10 20 30 ; run; /****JOINING THE REQUIRED COLUMNS****/ proc sql; create table final as select r.*,p.var1,p.var2,p.var3,q.var_x,q.var_y,q.var_z from a r left join other_var_table_1 p on r.int_t = p.int_t left join other_var_table_2 q on r.int_c = q.int_c order by r.seq; run;

You can drop column seq after the results if you want. Let me know if you have any doubts.

My Output:

int_c |int_t |SEQ |var1 |var2 |var3 |var_x |var_y |var_z 5 |3 |1 |12 |43 |76 |1 |2 |3 2 |9 |2 |. |. |. |100 |200 |300 15 |1 |3 |10 |20 |30 |. |. |. 65 |43 |4 |. |. |. |10 |20 |30