DATA Step, Macro, Functions and more

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

Reply
N/A
Posts: 0

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

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

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

Posted in reply to deleted_user
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.
N/A
Posts: 0

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

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

Thanks for your answer.
SAS Super FREQ
Posts: 8,862

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

Posted in reply to deleted_user
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
N/A
Posts: 0

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

Posted in reply to Cynthia_sas
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
Super Contributor
Super Contributor
Posts: 3,174

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

Posted in reply to deleted_user
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
Respected Advisor
Posts: 4,173

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

Posted in reply to deleted_user
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
N/A
Posts: 0

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

Thanks a lot Patrick! Smiley Happy
Ask a Question
Discussion stats
  • 7 replies
  • 289 views
  • 0 likes
  • 4 in conversation