- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;