DATA Step, Macro, Functions and more

Sql join to add variables to table A if there is a match in B

Reply
Frequent Contributor
Posts: 121

Sql join to add variables to table A if there is a match in B

Hi!  Looking for some help with a sql join.

 

Table A has all the observations I need (500 obs) so the final table should have 500 obs regardless of Table B. (which has 300K obs)

 

Table B has some additional variables I need only if the Parent_ID (Table B) match the DMID_ID in Table A.

 

I tried an inner join and left join but did not get correct output.  Help is appreciated!!

 

Proc sql;
create table mtf.mtf_size as
SELECT a.*, b.parent_size, b.dmis_id, b.facility_type_code, b.facility_FIPS_country_code, b.dmis_parent_id, b.facility_service_code
from dmisid a inner join mtf.facility_size_all b
on a.dmid_id=b.parent_id;
quit;

Super User
Posts: 5,081

Re: Sql join to add variables to table A if there is a match in B

Maybe you got the correct result and didn't know it.

 

What would you expect to get if Table B contains 10 records for each PARENT_ID?  Wouldn't you then get 5,000 records as the final result?

 

 

Frequent Contributor
Posts: 121

Re: Sql join to add variables to table A if there is a match in B

@AstoundingTable B just contains a list of Clinics and their specific features.  Each observation is a unique clinic (e.g.Parent_id).  Now table A could have multiple ID's  that match a single clinic (parent_id) in Table B...so the variables for that ID match would populate  table A.  So the end table would always need to be 500 obs.

Super User
Posts: 5,081

Re: Sql join to add variables to table A if there is a match in B

Or there could be fewer than 500, if there are some clinics in data set A that don't exist in data set B.

 

Why do you say that the results were incorrect? 

Frequent Contributor
Posts: 121

Re: Sql join to add variables to table A if there is a match in B

So, using my real numbers (rather than hypothetical) and my actual log...this is what I am seeing...The final table has far more observations than i want.

 

119  data dmisid;
120  set mtf;
121  parcost=coalescec (mtf, parcost);
122  run;

NOTE: There were 436487 observations read from the data set WORK.MTF.
NOTE: The data set WORK.DMISID has 436487 observations and 102 variables.

 

113  Proc sql;
114  create table mtf.mtf_size as
115  SELECT a.*, b.parent_size, b.dmis_id, b.facility_type_code, b.facility_name
116  from  dmisid a inner join mtf.facility_size_all b
117  on a.parcost=b.dmis_id;


NOTE: Table MTF.MTF_SIZE created, with 3692241 rows and 106 columns.

 

***Also note, Table B mtf.facility_size_all has 5267 observations(the unique clinics).

Frequent Contributor
Posts: 121

Re: Sql join to add variables to table A if there is a match in B

@AstoundingI think I found my error!  I have duplicate clinics in  table B and I think that was dublicating observations in table A

Ask a Question
Discussion stats
  • 5 replies
  • 271 views
  • 0 likes
  • 2 in conversation