topic Help in Rank function -SQL in SAS Programming

Help in Rank function -SQL

Kalai2008 — Wed, 27 Sep 2017 13:58:02 GMT

I know Rank function in SAS EG. But I am trying to get the data from Oracle. So I am using PROC SQL.
Given Data

ID Status Date
1 completed 09-SEP-16
1 null 08-SEP-16
1 Inprogress 07-JUL-16
1 NotCompleted 07-JUL-16
2 InProgress 09-sep-16
2 completed 09-sep-16
2 null 09-sep-16
2 n/a 05-AUG-16
2 Failed 10-JUN-16

Proc SQL;
Connect to oracle..
create table x as select * from connection to oracle

select
id, status, date,
row_number() over(partition by id order by date desc) seq
from y where seq=1;
quit;
run;

In the ID 2, I need the output as completed instead of Inprogress or null for the date 09-sep-16.
Is there any way I can able to get 'completed' status as my result?

Thanks

Re: Help in Rank function -SQL

ballardw — Wed, 27 Sep 2017 14:37:00 GMT

For that example input data what should the result look like?

And what is the rule for setting any specific "inprogess" to completed? Is it every instance or is it conditional in any way? Same question for "null".

Re: Help in Rank function -SQL

Kalai2008 — Wed, 27 Sep 2017 15:04:56 GMT

Thank you for checking!
The data I have provided is sample only. For ID2, I only need the status as 'completed'.
The rule is on the same date, let say on 09-SEP-16, If I see any status ..there can be 4 or 5 status, But whenever I see 'completed' in the (recent date only), my query should pick it. If I see 'completed' status on the 08-sep-16, I don't want them. That's why I am using the row_over function to capture the recent date status.

I don't want any rule to change the status from 'inprogress' to 'completed'.

The result should look like this

ID Status Date

1 completed 09-sep-16

2 completed 09-sep-16

3 inprogress 05-sep-16 .....

Re: Help in Rank function -SQL

LinusH — Wed, 27 Sep 2017 15:12:32 GMT

Since your are using explicit pass through it makes sence to have the full query there.
And the you are probably better off asking this question on an Oracle forum.

Re: Help in Rank function -SQL

HB — Wed, 27 Sep 2017 16:48:11 GMT

I agree with @LinusH that if you can do a query in Oracle and pass the result to SAS (or however that is done) that sounds like a better path.

Having said that, I present this cheat of a workaround:

*edited because I messed up a capitalization*

* get in some data;
Data project_status;
  input id:$1. status:$13. mydate:ddmmyy10.;
  datalines;
1 completed 090916
1 null 080916
1 Inprogress 070716
1 NotCompleted 070716
2 InProgress 090916
2 completed 090916
2 null 090916
2 n/a 050816
2 Failed 100616
3 inprogress 090616
3 started 080616
3 null 070616
4 failed 101017
;

*make a cheaty code table;
Data project_codes;
  input status_code:$1. project_status:$13.;
  datalines;
7 completed
4 inprogress
5 NotCompleted
1 null
2 n/a
6 Failed
3 started
;

*figure out where projects are by slecting the max code;
proc sql;
	create table last_status_code as
	select c.id, max(d.status_code) as last_status  
	from project_status c inner join project_codes d on c.status = d.project_status
	where c.mydate < 090916
	group by c.id
	;
quit;

*hook them back up;
proc sql;
	create table last_status_words as
	select a.id, b.project_status  
	from last_status_code a inner join project_codes b on a.last_status = b.status_code
	;
quit;

*lay it out;
proc sql;
	create table project_standing as
	select a.id, a.status, a.mydate  
	from project_status a inner join last_status_words b on a.id = b.id and a.status = b.project_status
	;
quit;

proc print noobs;
format mydate ddmmyy10.;
run;

This yields

                                        The SAS System                                     

                                 id     status          mydate

                                 1     completed    09/09/2016
                                 2     completed    09/09/2016
                                 3     inprogress   09/06/2016

My apologies.

Re: Help in Rank function -SQL

Kalai2008 — Wed, 27 Sep 2017 18:44:58 GMT

Thank you so much.

Re: Help in Rank function -SQL

HB — Wed, 27 Sep 2017 19:04:22 GMT

Keep in mind - garbage in, garbage out.

I think if project one has two completed status entries, for example, it blows up. (You might be able to look for max code and max date to try to fix that, I don't know). It will also blow up if you are looking for Completed and the status is completed.