BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
_LB
Fluorite | Level 6 _LB
Fluorite | Level 6
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
1 ACCEPTED SOLUTION

Accepted Solutions
DBailey
Lapis Lazuli | Level 10

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.

View solution in original post

11 REPLIES 11
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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
Ksharp
Super User
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



sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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.
Ksharp
Super User
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
DBailey
Lapis Lazuli | Level 10
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.
_LB
Fluorite | Level 6 _LB
Fluorite | Level 6
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
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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.
Quentin
Super User
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
BASUG is hosting free webinars Next up: Jane Eslinger presenting PROC REPORT and the ODS EXCEL destination on Mar 27 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.
DBailey
Lapis Lazuli | Level 10
quick point of clarification...you wouldn't need the group by using the ranked data.
DBailey
Lapis Lazuli | Level 10

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.

_LB
Fluorite | Level 6 _LB
Fluorite | Level 6
DBailey;
This helps immensely. Thank you.
Lawrence

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 11 replies
  • 48418 views
  • 0 likes
  • 5 in conversation