- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Posted 04-11-2010 07:23 AM
(4599 views)
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
[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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
+"proc sql" +"top n" +report site:sas.com
Scott Barry
SBBWorks, Inc.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
I did that, of course, but I didn't find a solution to my problem.
Thanks for your answer.
Thanks for your answer.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Thanks a lot Patrick! 🙂