DATA Step, Macro, Functions and more

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

Reply
Contributor
Posts: 47

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

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.

 

salesnew_sales
300300
-0.50
300300
-0.0010
0.20.2
-0.30
-0.4-0.4
-0.0009-0.0009
0.90.9
-0.007-0.007
PROC Star
Posts: 734

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

like this? Smiley Happy

 

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;
Contributor
Posts: 47

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

How to do the same using proc sql
PROC Star
Posts: 734

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

[ Edited ]

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

 

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

Super User
Posts: 11,343

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

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.

Ask a Question
Discussion stats
  • 4 replies
  • 138 views
  • 0 likes
  • 3 in conversation