DATA Step, Macro, Functions and more

Help in Rank function -SQL

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 94
Accepted Solution

Help in Rank function -SQL


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


Accepted Solutions
Solution
‎09-27-2017 02:45 PM
Super Contributor
Super Contributor
Posts: 266

Re: Help in Rank function -SQL

[ Edited ]

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.

View solution in original post


All Replies
Super User
Posts: 13,563

Re: Help in Rank function -SQL

Posted in reply to Kalai2008

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".

Frequent Contributor
Posts: 94

Re: Help in Rank function -SQL

[ Edited ]

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 .....

Super User
Posts: 5,883

Re: Help in Rank function -SQL

Posted in reply to Kalai2008
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.
Data never sleeps
Solution
‎09-27-2017 02:45 PM
Super Contributor
Super Contributor
Posts: 266

Re: Help in Rank function -SQL

[ Edited ]

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.

Frequent Contributor
Posts: 94

Re: Help in Rank function -SQL

Thank you so much. 

Super Contributor
Super Contributor
Posts: 266

Re: Help in Rank function -SQL

[ Edited ]
Posted in reply to Kalai2008

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.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 317 views
  • 1 like
  • 4 in conversation