BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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
7 REPLIES 7
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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
Not applicable
I did that, of course, but I didn't find a solution to my problem.

Thanks for your answer.
Cynthia_sas
SAS Super FREQ
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
Not applicable
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
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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
Opal | Level 21
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
Not applicable
Thanks a lot Patrick! 🙂

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 7 replies
  • 3969 views
  • 0 likes
  • 4 in conversation