topic Re: SQL NULL JOINs in SAS Programming

SQL NULL JOINs

kenjichan1212 — Fri, 06 Sep 2019 01:00:16 GMT

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

Re: SQL NULL JOINs

novinosrin — Fri, 06 Sep 2019 01:13:48 GMT


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;

Re: SQL NULL JOINs

Patrick — Fri, 06 Sep 2019 01:48:48 GMT

@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;

Re: SQL NULL JOINs

PGStats — Fri, 06 Sep 2019 01:29:10 GMT

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;