BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
michokwu
Quartz | Level 8

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_idh_idS000w_xw_yh_xh_  IDPOINT_XPOINT_Y
2951010110010032909970140410471      295101011001003737017.8354270859.993
2951010110010252951010110010251      295101011001025737539.9604270379.588
2951010110010262918795070030381      295101011001026737669.5944270307.737
2951010110010292918921990060061      295101011001029736570.0544270925.217
2951010110020072951010110020151      295101011002007737935.1074271151.965
2951010110020142951010110010031      295101011002014738029.0914270895.108
2951010110020162951010110010071      295101011002016738184.0394271047.110
2951010110020302951012550010081      295101011002030738075.5704270987.989
2951010120010032951011650050041      295101012001003737738.9874271653.824
2951010120010132918922133220091      295101012001013737987.6074271525.811
2951010120010152918921190040021      295101012001015737720.0164271341.538
2951010120010172951010130030321      295101012001017737390.2874271360.476
2951010120010191711940280240031      295101012001019737593.1864271268.272
2951010120020092918331030110011      295101012002009737080.9474271826.731
2951010120020132951010120010021      295101012002013737253.3294271491.580
2951010120030001711940190321831      295101012003000737584.5824272481.255
2951010120030022909970011320211      295101012003002737443.1894272411.160
2951010120030042951010120020101      295101012003004737515.4294272332.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;

 

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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)

PG

View solution in original post

2 REPLIES 2
PGStats
Opal | Level 21

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)

PG
michokwu
Quartz | Level 8

Thank you so much!

hackathon24-white-horiz.png

Join the 2025 SAS Hackathon!

Calling all data scientists and open-source enthusiasts! Want to solve real problems that impact your company or the world? Register to hack by August 31st!

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
  • 2 replies
  • 846 views
  • 0 likes
  • 2 in conversation