Make value of column to 0 if it is between 0 to -1, but only 3 time as shown below.

[subrat1]

i have one column named sales, I want to create a new column sales , but new column value become 0 if it is between 0 to -1, but only 3 time as shown below.

sales	new_sales
300	300
-0.5	0
300	300
-0.001	0
0.2	0.2
-0.3	0
-0.4	-0.4
-0.0009	-0.0009
0.9	0.9
-0.007	-0.007

[PeterClemmensen]

like this? 🙂

data have;
   infile datalines;
   input sales;
   datalines;
300
-0.5
300
-0.001
0.2
-0.3
-0.4
-0.0009
0.9
-0.007
;

data want(drop = count);
   set have;
   
   newsales = sales;

   if -1 <= sales & sales <= 0 & count < 3 then do;
      newsales = 0;
      count + 1;
   end;

   retain count;
run;

[subrat1]

How to do the same using proc sql

[PeterClemmensen]

First off, why would you want to do this in proc sql? 🙂

Doing this in a data step is much simpler and much more understandable 

[ballardw]

SQL does not have a natural "order" of operating on data. To force specific sequence processing can be extremely difficult and/or require much more code for sequential processing.