proc sql "top x" in subquery 1

[SicAIvanov]

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

 

[kdt82]

Hi,

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

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.

*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;