Look up multiple variables

[michokwu]

Hello experts,

I need to use the XY_CORD table to complete the DATA table with w & h  XY values. I used proc sql (see code) to look up the values for w and h separately, then merged both files but I would appreciate a more efficient way to look up both values simultaneously. Thank you

DATA			XY_CORD
w_id	h_id	S000	w_x	w_y	h_x	h_			ID	POINT_X	POINT_Y
295101011001003	290997014041047	1							295101011001003	737017.835	4270859.993
295101011001025	295101011001025	1							295101011001025	737539.960	4270379.588
295101011001026	291879507003038	1							295101011001026	737669.594	4270307.737
295101011001029	291892199006006	1							295101011001029	736570.054	4270925.217
295101011002007	295101011002015	1							295101011002007	737935.107	4271151.965
295101011002014	295101011001003	1							295101011002014	738029.091	4270895.108
295101011002016	295101011001007	1							295101011002016	738184.039	4271047.110
295101011002030	295101255001008	1							295101011002030	738075.570	4270987.989
295101012001003	295101165005004	1							295101012001003	737738.987	4271653.824
295101012001013	291892213322009	1							295101012001013	737987.607	4271525.811
295101012001015	291892119004002	1							295101012001015	737720.016	4271341.538
295101012001017	295101013003032	1							295101012001017	737390.287	4271360.476
295101012001019	171194028024003	1							295101012001019	737593.186	4271268.272
295101012002009	291833103011001	1							295101012002009	737080.947	4271826.731
295101012002013	295101012001002	1							295101012002013	737253.329	4271491.580
295101012003000	171194019032183	1							295101012003000	737584.582	4272481.255
295101012003002	290997001132021	1							295101012003002	737443.189	4272411.160
295101012003004	295101012002010	1							295101012003004	737515.429	4272332.304

/*For w locations*/
proc sql;
create table coord as
select *
from me.data, me.xy_cord
where data.w_id=xy_cord.id
order by data.w_id;
quit;

[PGStats · Accepted solution]

Try this:

proc sql;
create table coord as
select 
	a.w_id,
	a.h_id,
	a.S000,
	b.point_x as w_x,
	b.point_y as w_y,
	c.point_x as h_x,
	c.point_y as h_y
from 
	me.data as a left join 
	me.xy_cord as b on a.w_id=b.id left join
	me.xy_cord as c on a.h_id=c.id
order by w_id;
quit;

(untested)

[michokwu]

Thank you so much!