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!
Can anyone help with the appropriate code?
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.
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
@Nirmol did you try my code?
@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
????
Anyway, thanks for trying to help! I am good now.
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;
Thank you, it worked!
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
Thanks!
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.