BookmarkSubscribeRSS Feed
jenim514
Pyrite | Level 9

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;

5 REPLIES 5
Astounding
PROC Star

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?

 

 

jenim514
Pyrite | Level 9

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

Astounding
PROC Star

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? 

jenim514
Pyrite | Level 9

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

jenim514
Pyrite | Level 9

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 961 views
  • 0 likes
  • 2 in conversation