SQL NULL JOINs

[kenjichan1212]

Hi I have two tables as follows:

Table1

Year      Category     Admissions

2011    Category 1    0

2011    Category 2    0

2012    Category 1    0

2012    Category 2    0

2013    Category 1    0

2013    Category 2    20

Table2

Year      Category     Admissions

2013    Category 2    20

How do I get a table looking as follows:

2013    Category 1    0

2013    Category 2    20

I tried left joining them by Year but it gets rid of the zeros for 2013 Category 1. Sorry any help is appreciated

[novinosrin]

data table1;
input Year      Category & $15.    Admissions ;
cards;
2011    Category 1    0
2011    Category 2    0
2012    Category 1    0
2012    Category 2    0
2013    Category 1    0
2013    Category 2    20
;
 

data table2;
input Year      Category & $15.    Admissions ;
cards;
2013    Category 2    20
;

 
proc sql;
create table want as
select a.* ,b.admissions as table1_admissions
from table2 a left join table1 b
on a.year=b.year ;
quit;

[PGStats]

Or, if you can afford to name all variables:

proc sql;
create table want as
select 
	b.Year, 
	a.Category,
	a.Admissions
from table2 a left join table1 b
on a.year=b.year ;
quit;

[Patrick · Accepted solution]

@kenjichan1212 

If I understood your problem correctly then below should do the job. What it does: Select from table1 all rows with a matching year in table2.

data table1;
  infile datalines truncover dlm=',';
  input year category:$20. admission;
  datalines;
2011,Category 1,0
2011,Category 2,0
2012,Category 1,0
2012,Category 2,0
2013,Category 1,0
2013,Category 2,20
;
run;

data table2;
  infile datalines truncover dlm=',';
  input year category:$20. admission;
  datalines;
2013,Category 2,20
;
run;

 
proc sql;
/*  create table want as*/
  select t1.*
  from table1 t1
  where exists
    (select * from table2 t2 where t2.year=t1.year)
  ;
quit;