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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.