Help using Base SAS procedures

PROC SQL equivalent of TOP

Reply
Regular Contributor
Regular Contributor
Posts: 170

PROC SQL equivalent of TOP

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
Super Contributor
Super Contributor
Posts: 3,174

Re: PROC SQL equivalent of TOP

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
Super User
Posts: 9,662

Re: PROC SQL equivalent of TOP

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



Super Contributor
Super Contributor
Posts: 3,174

Re: PROC SQL equivalent of TOP

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.
Super User
Posts: 9,662

Re: PROC SQL equivalent of TOP

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
Super Contributor
Posts: 578

Re: PROC SQL equivalent of TOP

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.
Regular Contributor
Regular Contributor
Posts: 170

Re: PROC SQL equivalent of TOP

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
Super Contributor
Super Contributor
Posts: 3,174

Re: PROC SQL equivalent of TOP

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.
PROC Star
Posts: 1,226

Re: PROC SQL equivalent of TOP

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
Super Contributor
Posts: 578

Re: PROC SQL equivalent of TOP

quick point of clarification...you wouldn't need the group by using the ranked data.
Super Contributor
Posts: 578

Re: PROC SQL equivalent of TOP

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.
Regular Contributor
Regular Contributor
Posts: 170

Re: PROC SQL equivalent of TOP

DBailey;
This helps immensely. Thank you.
Lawrence
Ask a Question
Discussion stats
  • 11 replies
  • 25609 views
  • 0 likes
  • 5 in conversation