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.
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.
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
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
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.
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
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.
Thqs Michelle and patrick for your help
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: