topic Re: Please help: how to fetch top 1 value within select statement in SAS Programming

Please help: how to fetch top 1 value within select statement

deleted_user — Sun, 11 Apr 2010 11:23:49 GMT

What is the equivalent of "top n" within SAS? I'm having trouble with defining the statement pasted below:

[pre]SELECT A.COLUMN1, A.COLUMN2, A.COLUMN3,
(SELECT TOP 1 B.COLUM4 FROM TABLE_B AS B
WHERE A.COLUMN1 = SUBSTR(B.COLUMN1,1,17)) AS XX
FROM TABLE_A AS A[/pre]

Your help would be much appreciated. Message was edited by: P12345678

Re: Please help: how to fetch top 1 value within select statement

sbb — Sun, 11 Apr 2010 12:27:42 GMT

Suggest using Google advanced search argument below with the SAS support website resources.

+"proc sql" +"top n" +report site:sas.com

Scott Barry
SBBWorks, Inc.

Re: Please help: how to fetch top 1 value within select statement

deleted_user — Sun, 11 Apr 2010 13:03:11 GMT

I did that, of course, but I didn't find a solution to my problem.

Thanks for your answer.

Re: Please help: how to fetch top 1 value within select statement

Cynthia_sas — Sun, 11 Apr 2010 13:51:22 GMT

Hi:
Using Scott's Google search, this Tech Support note was one of the hits:
http://support.sas.com/kb/30/867.html

and it shows a PROC FREQ approach to find the top N (along with Order=Freq) to display the FREQ results in descending order.

and this paper (another hit)
http://www2.sas.com/proceedings/sugi25/25/aa/25p006.pdf

shows a PROC SQL approach for top N.

cynthia

Re: Please help: how to fetch top 1 value within select statement

deleted_user — Sun, 11 Apr 2010 14:12:34 GMT

I've tried all of the solutions I found using google search, like outobs, fetchobs, etc. but I always get an error (I'm probably not using it correctly)...

So if anyone can edit the short code I pasted, and not only paste links, it would be greatly appreciated....

I also tried with distinct (since I know all the values per certain joining record are the same and I'm going to get one distinct value from table B per record in table A), but it takes hours to process in SAS EG and I have to kill it eventually. The 'A' table has more than 2 million rows.

SELECT A.COLUMN1, A.COLUMN2, A.COLUMN3,
(SELECT DISTINCT B.COLUM4 FROM TABLE_B AS B
WHERE A.COLUMN1 = SUBSTR(B.COLUMN1,1,17)) AS XX
FROM TABLE_A AS A

Re: Please help: how to fetch top 1 value within select statement

sbb — Sun, 11 Apr 2010 14:55:27 GMT

Share your SAS-generated log with whatever error your are receiving - that would be more helpful to address any SAS program problem. Also, start out with testing your program by either using the SASHELP sample datasets and/or you can build your own SAS sample data by reading a subset of your current master file to create a WORK file and then run/test your program.

Regarding your post-reply, the SAS program code was likely truncated (due to characters you may have included with it) so read this "guidelines" suggestion post and then go back and EDIT your post or post another reply with your log and any errors along with code executed:

http://support.sas.com/forums/thread.jspa?messageID=27609

Scott Barry
SBBWorks, Inc. Message was edited by: sbb

Re: Please help: how to fetch top 1 value within select statement

Patrick — Mon, 12 Apr 2010 09:53:16 GMT

There are various ways in SAS to do this. Here a SQL approach:

data have;
do GroupId=1 to 5;
do id=1 to 10;
var=ceil(ranuni(0)*50);
output;
end;
end;
run;

proc sql;
select a.*
from have as a inner join
(select groupid,max(var) as var
from have
group by groupid) as b
on a.groupid=b.groupid and a.var=b.var
;
quit;

HTH
Patrick

Re: Please help: how to fetch top 1 value within select statement

deleted_user — Mon, 12 Apr 2010 16:22:41 GMT

Thanks a lot Patrick! 🙂