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]
"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.
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.
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: