merging tables by using PROC SQL where in statement in SAS

Question

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,

Answer 1

/****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