How to use limit observations in SAS when union two tables

Question

I'm using sas and I want to limit number of output rows for each table after order the data source, can anyone tell me how to achieve that in SAS? I know in mysql I can just use limit to do the work, but in SAS if I use (obs=10) or (outobs =10), it just limit the number of data input. Here is my proc sql

select distinct sales as a from lucas
group by province 
outer union
select distinct sales as b from lucas
group by province
order by a desc, b asc; 

Answer 1

Normally you would just use OBS= option when reading the data.

data top10;
  set have (obs=10);
  by size descending;
run;

If you don't already have a dataset sorted in that order and you want to avoid writing the full dataset out you could use a VIEW to do the generation and/or ordering for you.

proc sql ;
create view derived_sales as
  select id,sum(sales) as total_sales
  from have 
  group by id 
  order by calculated total_sales desc
;
quit;
data top10_sales;
  set derived_sales(obs=10);
run;

Answer 2

Proc SQL does not implement current modern clauses such a LIMIT, OFFSET, FETCH, nor does it have partitioning functions you may be familiar with.

That said, you can not row limit the output of a sorted sub select or view, however, you can limit the output to a table using the OUTOBS option.

This sample creates two tables, each corresponding to a sub-select limiting 10 rows of a sorted result set. The option is reset prior to unioning them.

proc sql;

  reset outobs=10;

  create table have_ss1 as
  select distinct msrp as msrp_1 
   from sashelp.cars 
   group by model
  ;

  create table have_ss2 as 
  select distinct msrp as msrp_2
   from sashelp.cars 
   group by model
  ;

  reset outobs=&sysmaxlong;

  create table want as 
  select * from have_ss1
  outer union
  select * from have_ss2
  ;

The SAS log window will show informative warnings, such as:

WARNING: A GROUP BY clause has been transformed into an ORDER BY clause because neither the
         SELECT clause nor the optional HAVING clause of the associated table-expression
         referenced a summary function.
WARNING: The query as specified involves ordering by an item that doesn't appear in its SELECT
         clause. Since you are ordering the output of a SELECT DISTINCT it may appear that some
         duplicates have not been eliminated.
WARNING: Statement terminated early due to OUTOBS=10 option.

Answer 3

I would do it like that as this limits the dataset/table created in the proc sql and not the input from the lucas dataset/table :

proc sql outobs=10;
select distinct sales as a from lucas
group by province 
outer union
select distinct sales as b from lucas
group by province
order by a desc, b asc; 
quit;

this will only limit the output and not the input!