×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

proc sql "top x" in subquery

proc sql "top x" in subquery

proc sql "top x" in subquery

(OP)
Hello tek-tips users!

I want to use SAS to execute a SQL query.

What I had in mind (coming from T-SQL) is:

proc sql;
select id,
       (select top 1 address
        from   tab_b b
        where  b.id = a.id
        order by entry_date desc
       ) ad last_address
from   tab_a a;

Or, to explain verbally, given a list of id and a list of addresses, I want to select Id + the most recent address as output.

Unfortunately SAS SQL doesn't support the "top x" option (I thought it was standard SQL).

Things to notice:

-There is outobs= parameter, but it does not work for subqueries.
-I could add in the subquery another subquery (instead of order by -> "where id = (select max(id) from b where...)", but that would make my code very messy to debug and change in the future.

Is there a simple way to accomplish what I want with proc SQL?

Thanks in advance for your answers

-AI

RE: proc sql "top x" in subquery

Hi,

For your particular issue, I would try a subquery like this

CODE


proc sql;
select id,
       (select address
        from   tab_b b
        where  b.id = a.id
        having max(date)=date
       ) as last_address
from   tab_a a;

Here is some test data and code I used to get the last sale (by date) for a given year, to test the concept of the above code.

CODE

*Jumble data ;
data retail ;
  set sashelp.retail ;
  ran=ranuni(1234) ;
  run;
proc sort data=retail out=sales (drop=ran) ;
  by ran ;
  run;

*Get last sale by year ;
proc sql;
select *,
       (select sales
        from   sales b
        where  b.year = a.year
        having max(date)=date
       ) as last_sale
from  sales a
order by date ;
quit;

 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close