Q:

By Groups in SAS vs window functions in SQL

I have a table, input_table, that contains the following columns:
key - double
code - string
date- string
result -string

I have the following SAS code:
PROC SQL;
    CREATE TABLE t1 AS
    SELECT key, code, date, result
    from input_table
    ORDER BY key, code, date;
QUIT;

DATA t1;
   SET t1;
   final_result= INPUT(result, 5.)
RUN;

DATA t2;
   SET t1;
   WHERE NOT MISSING(final_result)
   BY key, code, date

   IF LAST.code;
RUN;

So from my understanding, this code is adding column 'final_result', which is result converted to a numerical value, or NULL if it contains non-numeric values. Then it selects rows that have the max date for each key, code pair. I tried to replicate this in hiveQL (which I think is pretty much the same as SQL for this case):
select key, code, date, result, final_result 
from
(select *, 
 row_number() over (partition by key, code order by date desc) as rnk, 
 cast(result as double) as final_result
 from input_table
) x
where rnk=1 and final_result is not null

Is this query equivalent to the SAS code above? (I would test myself but am having environment issues currently)

A:

The only major problem I see with this is that you may have an issue where the last row has a null/missing final result.
In SAS, the where clauses takes place before the data step processing, so it's effectively equivalent to having it in the partition statement (which I'm not sure if that's possible) and/or in an earlier step.  If the last row by date order happens to be null, SAS will skip it and take the last row that is not null (because it won't have been in the data stream to begin with).
In your SQL, though, if rank=1 happens to have final_result is null, it will be dropped - but the rank=2 or whatever row will not be kept (so you'd not have any rows for that particular key/code combination in your output).
select key, code, date, result, final_result 
from
(select s.*, 
 row_number() over (partition by key, code order by date desc) as rnk
 from (
   select *, cast(result as double) as final_Result
   from input_table 
   where final_Result is not null
   ) s
) x
where rnk=1

Something along those lines should be equivalent.
The only other potential issue I could see: If you have two rows with exactly equal dates, SAS will pick the "last" one in the input dataset order.  I don't know what Hive will do; in most SQL implementations, though, you should assume you will get one at random, as SQL won't attempt to preserve the row order.

