SAS Programming

DATA Step, Macro, Functions and more
BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
kenjichan1212
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
Patrick
Opal | Level 21

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

 

View solution in original post

3 REPLIES 3
novinosrin
Tourmaline | Level 20

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
Opal | Level 21

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;
PG
Patrick
Opal | Level 21

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

 

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 3 replies
  • 932 views
  • 1 like
  • 4 in conversation