join two tables using proc sql

[asinusdk]

Hi, I wonder what kind of joining this kind of table it is.
Could you please let me know proc sql code?



data a;
input year  id	v1;
cards;
1 1 23
2 1 25
3 1 26
1 3 27 
2 3 28
;

data b;
input year id v2 v3;
cards;
1 1 1 34 
2 1 0 35
3 1 1 245
1 2 0 234
2 2 1 325
3 2 0 3
1 3 1 25
2 3 0 23
3 3 1 34
;

The expected table looks like this.

year	id	numb	v1	v2
1	1	23	1	34
2	1	25	0	35
3	1	26	1	245
1	3	27	1	25
2	3	28	0	23
3	3	.	1	34

+addition: I want to extract all the information (v2 and v3 for all years 1-3) from table b

(even though there is one or two rows in table a) for only ids existing in table a. 

[PaigeMiller]

Because it is not clear why you include the last row in your desired output table, this desired output doesn't correspond to any built in JOIN in PROC SQL. If you can clearly state a reason why that last row is included, then it becomes easier to figure out how it can be programmed.

[asinusdk]

Thanks for your response.

I want to extract all the information (all years 1-3) from table b (even though there
is one or two rows in table a) for only ids existing in table a.

[novinosrin · Accepted solution]

data a;
input year  id	v1;
cards;
1 1 23
2 1 25
3 1 26
1 3 27 
2 3 28
;

data b;
input year id v2 v3;
cards;
1 1 1 34 
2 1 0 35
3 1 1 245
1 2 0 234
2 2 1 325
3 2 0 3
1 3 1 25
2 3 0 23
3 3 1 34
;

data want;
if 0 then merge a b;
call missing(v1);
merge a(in=a) b(in=b);
by id;
if a and b;
run;

[novinosrin]

Hi @asinusdk  Honestly the above is just to see data and attempt to get the output using some code I didn't pay attention to logical business need or objective as the question didn't reflect that. In other words, there is no what & why

[asinusdk]

I see... thanks, I should have added the explanation and logic. I'm sorry..

[asinusdk]

what if there are more variable in data a and I want to include these variables in the expected outcome?

[novinosrin]

did you try my code?

[FreelanceReinh]

---

@asinusdk wrote:

what if there are more variable in data a and I want to include these variables in the expected outcome?

---

Hi @asinusdk,

Try this:

data a;
input year id v1 morevar;
cards;
1 1 23 9
2 1 25 8
3 1 26 7
1 3 27 6
2 3 28 5
;

data b;
input year id v2 v3;
cards;
1 1 1 34 
2 1 0 35
3 1 1 245
1 2 0 234
2 2 1 325
3 2 0 3
1 3 1 25
2 3 0 23
3 3 1 34
;

proc sql;
create table want as
select b.year, b.id, v1 as numb, v2 as v1, v3 as v2, morevar
from a right join b
on a.id=b.id & a.year=b.year
where exists (select * from a where a.id=b.id)
order by id, year;
quit;

Edit:

Slightly shorter with

from a natural right join b

and without the ON clause.