join two tables ignore the missing values on joining

[sas_Forum]

i want to join two tables based two variables but i should not consider balnk values

data table1;
input id no;
cards;
6 1
5 .
5 .
7 2
run;

data table2;
input id no;
cards;
1 1
2 .
3 .
4 2
run;

i want  to join both table on no variable with no missing values(.) how can i do it.

[MichelleHomes]

If I understand your question that you want to join by the "no" variable and exclude missing values then you want something like...

*By putting the where statement in the proc sort then the sorted tables will not have any missing values for the merge;

proc sort data=table1 out=table1Sorted;

     by no;

     where no is not missing;

run;

proc sort data=table2 out=table2Sorted;

     by no;

     where no is not missing;

run;

*This will get what you want if it is a one to one or one to many type of merge. If it is many to many rows then replace the data step with SQL;

data newTable;

     merge table1Sorted(rename=(id=id1)) table2Sorted(rename=(id=id2));

     by no;

run;

*Depending on your data and if you have many to many records then you may want to do the join using SQL...;

proc sql;

     create table newTable as

     select t1.id as id1, t1.no, t2,id as id2

     from table1Sorted as t1, table2Sorted as t2

     where t1.no=t2.no

     ;

quit;

Hope this helps.

Cheers,

Michelle

[sas_Forum]

if i do the sort by where no not missinf the some records will be removed as i am having 10 million records in each table

[Patrick · Accepted solution]

If I interprete your requirement correct then below SQL should work.

proc sql;

/*  create table want as*/

    select l.no as l_no,r.no as r_no, l.id as l_id, r.id as r_id

    from table1 l full outer join table2 r

      on l.no=r.no and l.no not is null and r.no not is null

  ;

quit;

It would help a lot if you could show us how the required result table should look like.

[sas_Forum]

proc sql;
create table new2 as select * from table1 as a left join table2 as
b on a.no=b.no and a.no is not null and b.no is not null;
quit;

tnqs

yesterday i used   a.no is not null and not used b.no is not null

[MichelleHomes]

As Patrick said, if you are able to show what you want your joined table to look like that would help. We can then have a better understanding of your requirement and assist with the code.

[sas_Forum]

Thqs Michelle and patrick for your help