topic join two tables ignore the missing values on joining in SAS Programming

join two tables ignore the missing values on joining

sas_Forum — Wed, 29 Feb 2012 08:34:19 GMT

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.

Re: join two tables ignore the missing values on joining

MichelleHomes — Wed, 29 Feb 2012 09:08:35 GMT

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

join two tables ignore the missing values on joining

sas_Forum — Wed, 29 Feb 2012 10:28:46 GMT

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

Re: join two tables ignore the missing values on joining

Patrick — Wed, 29 Feb 2012 10:43:00 GMT

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.

join two tables ignore the missing values on joining

sas_Forum — Wed, 29 Feb 2012 10:59:26 GMT

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

Re: join two tables ignore the missing values on joining

MichelleHomes — Wed, 29 Feb 2012 12:25:57 GMT

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.

join two tables ignore the missing values on joining

sas_Forum — Wed, 29 Feb 2012 12:30:29 GMT

Thqs Michelle and patrick for your help