topic Re: PROC SQL equivalent of TOP in SAS Procedures

PROC SQL equivalent of TOP

_LB — Tue, 22 Mar 2011 16:41:25 GMT

Hello all;
Proc SQL in SAS does not seem to support the TOP function in SQL, is there an equivalent work around here? I get multiple records when I do a match but I just need the first record.

Thanks,

Lawrence

Re: PROC SQL equivalent of TOP

sbb — Tue, 22 Mar 2011 16:54:51 GMT

Check the OUTOBS= option with PROC SQL.

Scott Barry
SBBWorks, Inc.

Suggested Google advanced search argument, this topic / post:

proc sql select limit selected rows outobs site:sas.com

Re: PROC SQL equivalent of TOP

Ksharp — Wed, 23 Mar 2011 02:32:49 GMT

No.
outobs= option of sql only can output top several obs.
When Op want every top obs for every group ?

[pre]

[/pre]

proc sql;

create table top as

select region,sales

from sashelp.shoes

group by region

having
sales eq max(sales)

;

quit;

Ksharp

Re: PROC SQL equivalent of TOP

sbb — Wed, 23 Mar 2011 05:18:34 GMT

Clearly, Ksharp read more from the OP than I...however I just don't see how the solution SAS program yields a "top N by group variable list", frankly, unless "N" is a value equal to obs that have the MAX(SALES) value, which could be one or more observations within a particular group list.

Scott Barry
SBBWorks, Inc.

Re: PROC SQL equivalent of TOP

Ksharp — Wed, 23 Mar 2011 06:52:35 GMT

Hi.SBB.If You want solution SAS program yields a "top N by group variable list",then try tp
use proc univariate + nextrval option + class statement which I have mentioned before.

Ksharp

Re: PROC SQL equivalent of TOP

DBailey — Wed, 23 Mar 2011 11:06:08 GMT

If you want top N by group than you should just rank a variable by the given group. Then just choose how far down to go in the rank. Top 5 would be where rank<=5.

Re: PROC SQL equivalent of TOP

_LB — Wed, 23 Mar 2011 21:35:55 GMT

OK,
This thread is not helping the cause, as this is not what I am looking for-
The problem is that, yes ina deta step I can easily take the first observation by a given variable,

However, when the data is
name1 8/27/10
name1 9/15/10
name2 8/22/10
name2 10/22/10

I need to obtain the first record of each group so I only get:
name1 8/27/10
name2 8/22/10

I am not sure what you mean by ranking the variable. If I have to rank the date, then I have to do a proc sort first or after and that defeats the entire purpose.
Why SAS does not include the TOP function is unknown but annoying.

Sorry for the rants gents, just been one of those days..

Lawrence

Re: PROC SQL equivalent of TOP

sbb — Wed, 23 Mar 2011 22:23:00 GMT

From what I read, consider that the TOP clause is not ANSI SQL standard, so it would be implemented various ways possibly, based on the DBMS system used.

Scott Barry
SBBWorks, Inc.

Re: PROC SQL equivalent of TOP

Quentin — Wed, 23 Mar 2011 23:51:38 GMT

Hi,

I'm confused as to why ksharp's solution doesnt work for you? Adapted to your sample data, I think it is:

proc sql;
create table want as
select name,date
from have
   group by name
    having date eq min(date)
     ;
quit;

This would return multiple records if you have dups, but easy enough to dedup.

If you want first 5 records per name, I would again agree with dbailey. Add a rank variable (could be done in a data step view, or perhaps a subquery with the undocumented monotonic() function), then use the rank to select records:

proc sql;
create table want as
select name,date
from have
   group by name
    having rank<(min(rank)+5)
     ;
quit;

Not sure what you mean by PROC sort messing things up. Remember, the SQL engine may be sorting data in the background anyway.

Above is all untested code, and i'm still learning SQL, so ymmv.

Kind Regards,
--Q. Message was edited by: Quentin

Re: PROC SQL equivalent of TOP

DBailey — Thu, 19 Oct 2017 12:24:41 GMT

If you are using the TOP syntax of T-SQL, then unless you include an order by statement, the rows are returned arbitrarily (see http://msdn.microsoft.com/en-us/library/ms189463.aspx). If you are including an order by, that suggests you want the one record returned that is the min (or max) of date column.

If this is representative of the data (tabA):

NameVal NameDate OtherVar1 OtherVar2
name1 8/27/10 A F
name1 9/15/10 B G
name2 8/22/10 C H
name2 8/22/10 C1 H1
name2 10/22/10 D I

proc sql;
	select
		a.nameval,
		a.namedate,
		a.othervar1,
		a.othervar2
	from tabA as A
		where a.NameDate = 
           (select min(NameDate) 
            from TabA where nameval = a.nameval);
quit;

This would return

NameVal NameDate OtherVar1 OtherVar2
name1 8/27/10 A F
name2 8/22/10 C H
name2 8/22/10 C1 H1

Note that this treats ties exactly like the top 1 statement would.

If that isn't what you're trying to do, then perhaps some clarification would help.

Re: PROC SQL equivalent of TOP

DBailey — Thu, 24 Mar 2011 02:08:09 GMT

quick point of clarification...you wouldn't need the group by using the ranked data.

Re: PROC SQL equivalent of TOP

_LB — Thu, 24 Mar 2011 14:57:58 GMT

DBailey;
This helps immensely. Thank you.
Lawrence