topic Re: Deleting a group based on condition in multiple column in SAS Programming

Deleting a group based on condition in multiple column

Nirmol — Thu, 24 Mar 2022 18:26:18 GMT

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!

Re: Deleting a group based on condition in multiple column

Nirmol — Thu, 24 Mar 2022 18:34:26 GMT

Can anyone help with the appropriate code?

Re: Deleting a group based on condition in multiple column

PaigeMiller — Thu, 24 Mar 2022 18:36:54 GMT

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.

Re: Deleting a group based on condition in multiple column

PeterClemmensen — Thu, 24 Mar 2022 18:45:31 GMT

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;

Re: Deleting a group based on condition in multiple column

Nirmol — Thu, 24 Mar 2022 18:54:05 GMT

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

Re: Deleting a group based on condition in multiple column

PeterClemmensen — Thu, 24 Mar 2022 18:57:12 GMT

@Nirmol did you try my code?

Re: Deleting a group based on condition in multiple column

PaigeMiller — Thu, 24 Mar 2022 19:19:38 GMT

@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

????

Re: Deleting a group based on condition in multiple column

yabwon — Thu, 24 Mar 2022 22:05:04 GMT

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

Re: Deleting a group based on condition in multiple column

Nirmol — Thu, 24 Mar 2022 22:19:50 GMT

Thank you, it worked!

Re: Deleting a group based on condition in multiple column

Nirmol — Thu, 24 Mar 2022 22:20:15 GMT

Thanks!

Re: Deleting a group based on condition in multiple column

Nirmol — Thu, 24 Mar 2022 22:21:07 GMT

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