Deal all:
Could anyone help to refine the code bellow to make it work?
Thanks in advance!!

/***************************/
proc sql;
create table asset_rel_estate as (
select collateral_nbr, col_case_nbr, estimate_date, boss_approve_date,
building_type_code, jcic_type_code,
rank() over (partition by collateral_nbr order by col_case_nbr desc) as
estate_seq
from vp.asset_rel_estate
where set_date is not null
qualify estate_seq = 1
);
quit;
/***************************/

hi, I am not expert on SQL, but I notice some strange statements as
"where set_date is not null
 qualify estate_seq = 1"

Could you please tell us how your dataset look like and the desired
output from it?

Perhaps you meant
where (set_date is not null
and  qualify is null and  estate_seq = 1)

Daniel Fernandez


2009/12/29 Stanley Luo <shanminglo@gmail.com>:
> Deal all:
> Could anyone help to refine the code bellow to make it work?
> Thanks in advance!!
>
> /***************************/
> proc sql;
> create table asset_rel_estate as (
> select collateral_nbr, col_case_nbr, estimate_date, boss_approve_date,
> building_type_code, jcic_type_code,
> rank() over (partition by collateral_nbr order by col_case_nbr desc) as
> estate_seq
> from vp.asset_rel_estate
> where set_date is not null
> qualify estate_seq = 1
> );
> quit;
> /***************************/
>