06-16-2016 01:30 PM
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!!
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
06-16-2016 01:57 PM
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?
06-16-2016 02:05 PM
@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.
06-16-2016 02:09 PM
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?
06-16-2016 02:19 PM
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);
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).