topic Re: Make value of column to 0 if it is between 0 to -1, but only 3 time as shown below. in SAS Programming

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

subrat1 — Mon, 06 Feb 2017 19:12:56 GMT

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

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

PeterClemmensen — Mon, 06 Feb 2017 19:48:00 GMT

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;

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

subrat1 — Tue, 07 Feb 2017 08:51:35 GMT

How to do the same using proc sql

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

PeterClemmensen — Tue, 07 Feb 2017 08:58:12 GMT

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

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

ballardw — Tue, 07 Feb 2017 15:34:58 GMT

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.