PROC SQL Join error?

[ybz12003]

Hi all:

I know it looks a little crazy and greedy.   I was trying to use PROC SQL inner join for merge 12 tables in ONE based on their same ID.  However, the code didn't work.  Any suggestion?  Thanks.

PROC SQL;
	CREATE TABLE test AS
	SELECT a.ID,
  a.Display_label,
  a.Crowdingpercent&year.,
  b.LowEducationpercent&year.,
  b.
.
. 
 l.AffordHousing_M&year.,
  l.AffordHousing_NoM&year.
	FROM crowding&year.  a inner JOIN
	education&year.  b inner JOIN
  .
.
.
.
.
  
  affordableHousing&year.  l
	ON a.ID = b.ID = c.ID = d.ID = e.ID = f.ID = g.ID = h.ID = i.ID = j.ID = k.ID = l.ID
	ORDER BY ID;
QUIT;

[SuryaKiran · Accepted solution]

Check you syntax: 

	FROM crowding&year.  a 
		inner JOIN education&year.  b ON a.ID = b.ID
		inner JOIN workercls&year.  c ON a.ID = c.ID
		inner JOIN occupation&year. d ON a.ID = d.ID 
		inner JOIN poverty&year.    e ON a.ID = e.ID
                ..............So on

[ybz12003]

Thanks, Suryakiran!  It works.

[ybz12003]

Thanks.  However, I found the ID are duplicated in the new combined table.  I have to add NODUPKEY step to fix it.   Is there a way to just do it in SQL step?  Thanks.

[mkeintz]

Then at least one of your initial tables must have a duplicate ID, right?  It might be better to first eliminate duplicates from the contributing table if you can come up with a criterion that doesn't need data from other tables.

[SuryaKiran]

You can remove duplicated by DISTINCT keyword in sub-query. Make sure you have all the columns selected in sub-query that are referenced in main query.

FROM crowding&year.  a 
		inner JOIN ( select distinct Display_label,Crowdingpercent&year.
                                   FROM education&year.) b ON a.ID = b.ID 
                inner JOIN workercls&year. c ON a.ID = c.ID 
                inner JOIN occupation&year. d ON a.ID = d.ID 
                inner JOIN poverty&year. e ON a.ID = e.ID ..............So on

[mkeintz]

@SuryaKiran:

I believe your suggestion would generate an

  "ERROR: Column ID could not be found in the table/view identified with the correlation name b."

message, because the sub-query doesn't include ID.

And if the subquery did include ID,as in

    (select distinct ID, Display_label,Crowdingpercent&year. FROM education&year.)

the OP could still get duplicate ID's in the resulting table, unless it is known that all instances of duplicate ID's in the education&year table will also have duplicate Display_label,Crowdingpercent&year values.

[SuryaKiran]

@mkeintz Your right. If you see my note I mentioned Make sure you have all the columns selected in sub-query that are referenced in main query.

[Reeza]

Could you append the data sets instead, add in a year column and then use TRANSPOSE to create the giant wide data set you want? The wide data set would be painful to work with anyways, so in general a vertical stacked data set may be preferable. 

---

@ybz12003 wrote:

Hi all:

I know it looks a little crazy and greedy.   I was trying to use PROC SQL inner join for merge 12 tables in ONE based on their same ID.  However, the code didn't work.  Any suggestion?  Thanks.

PROC SQL;
	CREATE TABLE test AS
	SELECT a.ID,
  a.Display_label,
  a.Crowdingpercent&year.,
  b.LowEducationpercent&year.,
  b.HighEducationpercent&year.,
  c.LowworkclsPercent&year.,
  d.Occupationpercent&year.,
  e.PovertyPercent&year.,
  f.RentalOccupancyPercent&year.,
  g.WealthHomepercent&year.,
  h.Unemploypercent&year.,
  i.MedianIncome&year.,
  j.Language2ndPercent&year.,
  k.White__P&year.,
  k.AA_P&year.,
  k.AIAN__P&year.,
  k.__P&year.,
  k.PI_P&year.,
  k.Other__P&year.,
  k.TwoPlus_P&year.,
  l.AffordHousing_M&year.,
  l.AffordHousing_NoM&year.
	FROM crowding&year.  a inner JOIN
	education&year.  b inner JOIN
  workercls&year.  c inner JOIN
  occupation&year.  d inner JOIN
  poverty&year.  e inner JOIN
  rentaloccupancy&year.  f inner JOIN
  wealthhome&year.  g inner JOIN
  unemploy&year.  h inner JOIN
  medianincome&year.  i inner JOIN
  language&year.  j inner JOIN
  race&year.  k inner JOIN
  affordableHousing&year.  l
	ON a.ID = b.ID = c.ID = d.ID = e.ID = f.ID = g.ID = h.ID = i.ID = j.ID = k.ID = l.ID
	ORDER BY ID;
QUIT;

---