topic Re: SQL JOIN to include subset of 1 table in SAS Programming

SQL JOIN to include subset of 1 table

RandoDando — Wed, 28 Apr 2021 18:58:34 GMT

I am trying to provide a subset of a table to someone using a PROC SQL query, but I am unsure of how to subset a table WITHIN a join. For instance

PROC SQL;

SELECT REG.REGION, ORG.ORG, PLC.PLACE, SPT.SPOT

from REGION REG INNER JOIN

ORGANIZATION ORG ON REG.REG_ID = ORG.REG_ID

INNER JOIN

PLACE PLC ON PLC.ORG_ID = ORG.ORG_ID

INNER JOIN

SPOT SPT ON SPT.PLACE_ID = PLC.PLACE_ID

where ORG.ORG_ID = 32158675;

quit;

If I want only the first 3 corresponding PLACE IDs in this example (using the order in the PLACE table), how do I modify this query to give me just that?

I prefer not to specify them in a where statement because I want to run this for different circumstances and it would require me to look up the possible IDs each time.

[Sorry for the boilerplate example, working from a different PC]

Re: SQL JOIN to include subset of 1 table

ballardw — Wed, 28 Apr 2021 19:44:02 GMT

"First 3" means that you may not want an SQL approach. SQL generally has no sense of "order".

Provide some examples of data and what you expect the result to be for those example data sets and you may get something.

Depending on how you define "first" you might be able to get them if 1) each ID only occurs one time in a data set and 2) the data set is sorted PRIOR to proc sql so that the first 3 (or other number) of desired values are in the first 3 records.

You can use SAS data set options on data sets that contribute to a Proc SQL query so options like (obs=3) will select the first 3 records from a data set. Here is an example using a data set you should have available to test code with.

proc sql;
   create table example as
   select *
   from sashelp.class (obs=3)
   ;
quit;

Repeating, you would have to have the data set with the values you want sorted before Proc SQL to use this approach to get "first" anything.

Re: SQL JOIN to include subset of 1 table

RandoDando — Wed, 28 Apr 2021 19:49:56 GMT

I am not looking for any values in particular, just the first three in lieu
of a random selection. There is only 1 record per org ID in the org table
(same for the other ID in their respective tables).

I have used a select query as a table in the joins in the past, so maybe I
could do some variation on that here?

Re: SQL JOIN to include subset of 1 table

ballardw — Wed, 28 Apr 2021 20:10:54 GMT

You have not defined what actually constitutes "first 3" of anything.

Or even which table you expect to get 3 from.

I cannot even tell if you are saying that selecting the first 3 from a specific contributing table is not sufficient. I have shown a way to do that. If you need something else you need to provide example data from all of your tables, with more than 3 so we can see where 3 are selected from, and the final result.

Re: SQL JOIN to include subset of 1 table

SAS-Nutzer — Thu, 29 Apr 2021 15:35:29 GMT

If each PLACE_ID is only supposed to fulfill the join condition once, then you can use this:

PROC SQL outobs=3;

SELECT REG.REGION, ORG.ORG, PLC.PLACE, SPT.SPOT

from REGION REG INNER JOIN

ORGANIZATION ORG ON REG.REG_ID = ORG.REG_ID

INNER JOIN

PLACE PLC ON PLC.ORG_ID = ORG.ORG_ID

INNER JOIN

SPOT SPT ON SPT.PLACE_ID = PLC.PLACE_ID where ORG.ORG_ID = 32158675;

quit;

Otherwise, use a temporary table:

proc sql noprint outobs=3;
create table PLACE_TEMP as select place_id, place
from PLACE;
quit;

PROC SQL;
SELECT REG.REGION, ORG.ORG, PLC.PLACE, SPT.SPOT
from REGION REG INNER JOIN
ORGANIZATION ORG ON REG.REG_ID = ORG.REG_ID
INNER JOIN
PLACE_TEMP PLC ON PLC.ORG_ID = ORG.ORG_ID
INNER JOIN
SPOT SPT ON SPT.PLACE_ID = PLC.PLACE_ID where ORG.ORG_ID = 32158675;
quit;

Re: SQL JOIN to include subset of 1 table

ChrisNZ — Thu, 29 Apr 2021 22:42:55 GMT

To avoid warnings, you can also use

proc sql noprint;
  create table PLACE_TEMP as select place_id, place
  from PLACE(ob=3);
quit;

instead of

proc sql noprint outobs=3;
  create table PLACE_TEMP as select place_id, place
  from PLACE;
quit;