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 |
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;
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
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.
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.