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

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

jenim514 — Thu, 16 Jun 2016 17:30:36 GMT

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;

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

Astounding — Thu, 16 Jun 2016 17:57:51 GMT

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?

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

jenim514 — Thu, 16 Jun 2016 18:05:48 GMT

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

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

Astounding — Thu, 16 Jun 2016 18:09:22 GMT

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?

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

jenim514 — Thu, 16 Jun 2016 18:19:21 GMT

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

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

jenim514 — Thu, 16 Jun 2016 18:49:38 GMT

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