Deleting a group based on condition in multiple column

[Nirmol]

Hi all

I have a data file with 3 variables (id, days and profit). id is the identifier of unique group and each group has 4 lines of observations. I have sorted the data by id and days. 

Now my target file is to have a file which will exclude ids which have profit of 0 for all days (day 1 to day 4). To put it differently, "want" file must not have those ids which have profit of 0 across all 4 days. In other words, if profit for all 4 days for certain id is 0, then I do not want to see it in my "want" file. 

Thanks in advance! 

 

[Nirmol]

Can anyone help with the appropriate code? 

[PaigeMiller]

UNTESTED CODE

proc sql;
    create table want as select *
    from have
    group by id
    having min(profit)=0 and max(profit)=0;
quit;

If you want tested code, data must be provided as text in SAS data step code. We can't test code from data in screen captures.

[Nirmol]

I tested this code, it does not work. It creates want file without any line of observations. Please, see the data file in text, here: 

            Have   

id         days     Profit 

101      1          0

101      2          0

101      3          0

101      4          0

117      1          5

117      2          -12

117      3          13

117      4          14

121      1          5

121      2          12

121      3          -13

121      4          18

135      1          0

135      2          15

135      3          16

135      4          0

142      1          17

142      2          23

All of these variables are in numeric (best12) format in sas,  

id            days       Profit

117         1              5

117         2              -12

117         3              13

117         4              14

121         1              5

121         2              12

121         3              -13

121         4              18

135         1              0

135         2              15

135         3              16

135         4              0

142         1              17

142         2              23

142         3              0

142         4              -8

Thanks again

[PeterClemmensen]

@Nirmol did you try my code?

[PaigeMiller]

---

@Nirmol wrote:

I tested this code, it does not work. It creates want file without any line of observations. Please, see the data file in text, here: 

---

This emphasizes the importance of providing data in a usable form (not screen captures).

So my code gave the 4 lines you didn't want, and didn't provide the rest of the lines you do want. This is a simple logic error on my part, here:

    having min(profit)=0 and max(profit)=0 ;

You want the opposite. So, homework assignment. What is the logical opposite of 

min(profit)=0 and max(profit)=0

????

[Nirmol]

Anyway, thanks for trying to help! I am good now. 

[PeterClemmensen]

data have;
input id days profit;
datalines;
101 1 0 
101 2 0 
101 3 0 
101 4 0 
117 1 5
117 2 -12 
117 3 13
117 4 14
121 1 5 
121 2 12 
121 3 -13
121 4 18
135 1 0 
135 2 15 
135 3 16
135 4 0 
142 1 17 
142 2 23
142 3 0
142 4 -8
;

data want;
   if _N_ = 1 then do;
      dcl hash h(dataset : 'have(where = (profit))');
      h.definekey('id');
      h.definedone();
   end;
   
   set have;
   
   if h.check() = 0;
run;

[Nirmol]

Thank you, it worked!

[yabwon]

I'd say "perfect use case for double DoW-loop.

data have;
input id days profit;
datalines;
101 1 0 
101 2 0 
101 3 0 
101 4 0 
117 1 5
117 2 -12 
117 3 13
117 4 14
121 1 5 
121 2 12 
121 3 -13
121 4 18
135 1 0 
135 2 15 
135 3 16
135 4 0 
142 1 17 
142 2 23
142 3 0
142 4 -8
;
run;

data want;
  do _N_= 1 by 1 until(last.id);
    set have;
    by id;
    _IORC_ + abs(profit);
  end;

  do _N_ = 1 to _N_;
    set have;
    if _IORC_ then output;
  end;
  _IORC_ = 0;
run;
proc print;
run;

Bart

[Nirmol]

Thanks!