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: 4,173

Re: join two tables ignore the missing values on joining

Posted in reply to sas_Forum

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,322

Re: join two tables ignore the missing values on joining

Posted in reply to sas_Forum

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

Posted in reply to MichelleHomes

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: 4,173

Re: join two tables ignore the missing values on joining

Posted in reply to sas_Forum

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,322

Re: join two tables ignore the missing values on joining

Posted in reply to sas_Forum

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

Posted in reply to MichelleHomes

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
  • 2356 views
  • 3 likes
  • 3 in conversation