topic Re: PROC SQL Join error? in SAS Procedures

PROC SQL Join error?

ybz12003 — Wed, 23 May 2018 19:43:55 GMT

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;

Re: PROC SQL Join error?

SuryaKiran — Wed, 23 May 2018 18:09:43 GMT

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

Re: PROC SQL Join error?

ybz12003 — Wed, 23 May 2018 18:35:12 GMT

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.

Re: PROC SQL Join error?

mkeintz — Wed, 23 May 2018 18:42:26 GMT

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.

Re: PROC SQL Join error?

SuryaKiran — Wed, 23 May 2018 18:50:47 GMT

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

Re: PROC SQL Join error?

Reeza — Wed, 23 May 2018 19:02:03 GMT

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;

Re: PROC SQL Join error?

mkeintz — Wed, 23 May 2018 19:16:57 GMT

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

Re: PROC SQL Join error?

ybz12003 — Wed, 23 May 2018 19:20:29 GMT

Thanks, Suryakiran! It works.

Re: PROC SQL Join error?

SuryaKiran — Wed, 23 May 2018 19:28:22 GMT

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