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

[deleted_user]

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

[sbb]

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.

[deleted_user]

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

Thanks for your answer.

[Cynthia_sas]

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

[deleted_user]

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

[sbb]

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

[Patrick]

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

[deleted_user]

Thanks a lot Patrick! 🙂