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;
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?
@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.
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?
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).
@AstoundingI think I found my error! I have duplicate clinics in table B and I think that was dublicating observations in table A
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.