0

Sorry for the misleading (if there is) question title, as I don't know how to express what I need in 1 question.

I have a dataset as below:

UserId    Order    Status
1         1        completed
1         2        completed
1         3        incompleted
2         1        incompleted
2         2        incompleted

I want to select those users who have no completed status, for example, with the above dataset the result I get would be

UserId
2

Is there anyway to get the above result with SAS datastep or PROC SQL? Help is appreciated and thanks in advance.

Luke Henz
  • 137
  • 3
  • 5
  • 12

2 Answers2

3

For this you can use NOT IN clause, as below - 

SELECT DISTINCT Userid
  FROM Your_Table_Name
 WHERE Userid NOT IN
       (SELECT Userid FROM Your_Table_Name WHERE Status = 'completed')
pratik garg
  • 3,282
  • 1
  • 17
  • 21
2

Here's a data step approach, the proc sort will not be necessary if your data is already sorted.

data have;
input UserId Order Status :$11.;
datalines;
1         1        completed
1         2        completed
1         3        incompleted
2         1        incompleted
2         2        incompleted
;
run;

proc sort data=have;
by userid order;
run;

data want (keep=userid);
set have;
by userid;
if first.userid then num_complete=0;
num_complete+(status='completed');
if last.userid and num_complete=0 then output;
run;
Longfish
  • 7,582
  • 13
  • 19
  • Hey Keith, thanks for your answer. I've been using it and a small problem emerged, if "status" variable has more than 2 values, then can we still do it this way? For example, "status" has 1 more value "unknown". Thanks in advance. – Luke Henz Jun 10 '13 at 03:15
  • So do you want to count the number of unknowns as well? – Longfish Jun 10 '13 at 07:44
  • sorry for the late response, but I have figured it out. Thanks again for your answer. Your code helped me a lot. – Luke Henz Jun 17 '13 at 04:46