SicAIvanov
Technical User
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
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