topic Re: join two tables using proc sql in SAS Programming

join two tables using proc sql

asinusdk — Sun, 21 Oct 2018 20:11:40 GMT

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.

Re: join two tables using proc sql

PaigeMiller — Sun, 21 Oct 2018 19:39:01 GMT

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.

Re: join two tables using proc sql

asinusdk — Sun, 21 Oct 2018 19:54:18 GMT

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.

Re: join two tables using proc sql

novinosrin — Sun, 21 Oct 2018 20:00:23 GMT

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;

Re: join two tables using proc sql

novinosrin — Sun, 21 Oct 2018 20:02:12 GMT

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

Re: join two tables using proc sql

asinusdk — Sun, 21 Oct 2018 20:14:06 GMT

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

Re: join two tables using proc sql

asinusdk — Sun, 21 Oct 2018 20:14:46 GMT

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

Re: join two tables using proc sql

novinosrin — Sun, 21 Oct 2018 20:19:06 GMT

did you try my code?

Re: join two tables using proc sql

FreelanceReinh — Sun, 21 Oct 2018 20:32:10 GMT

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