BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Kalai2008
Pyrite | Level 9


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

1 ACCEPTED SOLUTION

Accepted Solutions
HB
Barite | Level 11 HB
Barite | Level 11

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

6 REPLIES 6
ballardw
Super User

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

Kalai2008
Pyrite | Level 9

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

LinusH
Tourmaline | Level 20
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
HB
Barite | Level 11 HB
Barite | Level 11

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.

Kalai2008
Pyrite | Level 9

Thank you so much. 

HB
Barite | Level 11 HB
Barite | Level 11

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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