DATA Step, Macro, Functions and more

join two tables ignore the missing values on joining

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 140
Accepted Solution

join two tables ignore the missing values on joining

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.


Accepted Solutions
Solution
‎02-29-2012 05:43 AM
Respected Advisor
Posts: 3,908

Re: join two tables ignore the missing values on joining

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.

View solution in original post


All Replies
Trusted Advisor
Posts: 1,253

Re: join two tables ignore the missing values on joining

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

Frequent Contributor
Posts: 140

join two tables ignore the missing values on joining

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

Solution
‎02-29-2012 05:43 AM
Respected Advisor
Posts: 3,908

Re: join two tables ignore the missing values on joining

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.

Frequent Contributor
Posts: 140

join two tables ignore the missing values on joining

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

Trusted Advisor
Posts: 1,253

Re: join two tables ignore the missing values on joining

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.

Frequent Contributor
Posts: 140

join two tables ignore the missing values on joining

Thqs Michelle and patrick for your help

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 1819 views
  • 3 likes
  • 3 in conversation