A couple of suggestions: first peculiar to this forum software, you should paste code, Log or text data examples, into a text box opened on the forum using the </> icon that appears above the message window. The forum main message windows reformat pasted text and may result in removing, replacing or inserting characters such that when pasted into a code editor will not run correctly. See the box below.
Second is an SQL "trick" to reduce typing AND make code a little more flexible: table alias. You can provide a much shorter name to reference your tables as shown below with the <dataset name> as <alias>. Then you don't have to type long data set names constantly. Also you can test/run code with a different data set and only have to change the name in one place where the Alias is set. I highlighted one example for the first data set From. The "as" is actually optional in many SQL implementations but I like to see it so it is clear what T1 (a semi-traditional alias for "Table One"). I could replace the "national_downloadable_file" with "testset1" for example and no other code would need to change. This is very useful for testing a small set of values (possibly of particular interest for some reason) or just to verify that syntax works on a small set before turning it loose on larger data.
proc sql;
SELECT DISTINCT
T1.group_practice_PAC_id,
T1.number_of_group_practice_members,
T3.provider_npi,
T5.cbsa
FROM
national_downloadable_file as T1
JOIN
provider_intersect as T2 ON
T1.national_downloadable_file_id =
T2.national_downloadable_file_id
LEFT JOIN
ep_provider_paid_ehr as T3 ON
T2.ep_provider_paid_ehr_id =
T3.ep_provider_paid_ehr_id
JOIN
zipcode_table as T4 ON
T1.zipcode =
T4.zipcode
LEFT JOIN
zip_to_cbsa as T5 ON
T4.zipcode = T5.zipcode
WHERE T1.group_practice_PAC_id<>'NA'
;
quit;
As a side-effect of setting aliases in your code it become obvious that your code uses 5 separate tables and you apparently only have 3. So anything using those extra tables would have to "go away" OR provide the data sets.
... View more
