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
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.
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".
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 .....
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.
Thank you so much.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.