Help using Base SAS procedures

PROC SQL Join error?

Accepted Solution Solved
Reply
Super Contributor
Posts: 398
Accepted Solution

PROC SQL Join error?

[ Edited ]

Hi all:

 

I know it looks a little crazy and greedy. Cat Tongue  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;

 


Accepted Solutions
Solution
‎05-23-2018 03:20 PM
Valued Guide
Posts: 597

Re: PROC SQL Join error?

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
Thanks,
Suryakiran

View solution in original post


All Replies
Solution
‎05-23-2018 03:20 PM
Valued Guide
Posts: 597

Re: PROC SQL Join error?

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
Thanks,
Suryakiran
Super Contributor
Posts: 398

Re: PROC SQL Join error?

Posted in reply to SuryaKiran

Thanks, Suryakiran!  It works.

Super Contributor
Posts: 398

Re: PROC SQL Join error?

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.

Trusted Advisor
Posts: 1,345

Re: PROC SQL Join error?

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.

 

Valued Guide
Posts: 597

Re: PROC SQL Join error?

[ Edited ]

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
Thanks,
Suryakiran
Trusted Advisor
Posts: 1,345

Re: PROC SQL Join error?

Posted in reply to SuryaKiran

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

 

 

Valued Guide
Posts: 597

Re: PROC SQL Join error?

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

Thanks,
Suryakiran
Super User
Posts: 23,771

Re: PROC SQL Join error?

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. Cat Tongue  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;

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 241 views
  • 2 likes
  • 4 in conversation