BookmarkSubscribeRSS Feed
Nirmol
Fluorite | Level 6

question.PNG

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! 

 

10 REPLIES 10
Nirmol
Fluorite | Level 6

Can anyone help with the appropriate code? 

PaigeMiller
Diamond | Level 26

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.

--
Paige Miller
Nirmol
Fluorite | Level 6

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

 

PaigeMiller
Diamond | Level 26

@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

????

--
Paige Miller
Nirmol
Fluorite | Level 6

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

PeterClemmensen
Tourmaline | Level 20
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
Fluorite | Level 6

Thank you, it worked!

yabwon
Onyx | Level 15

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

_______________
Polish SAS Users Group: www.polsug.com and communities.sas.com/polsug

"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings

SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation



Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
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
  • 10 replies
  • 979 views
  • 1 like
  • 4 in conversation