Hi All,
I realy need an urgent help on the below.
I have got a dataset as below and I want to create a counter when sal goes from positive to negative and counter increases to 1 but not for all negative sal .I have explained as below.
for example in the below example for ID A01 sal went from postive to negative only two times so I need to create this counter for every ID
Has | Want | |||
ID | Sal | ID | Count | |
A01 | 261.56 | A01 | 2 | |
A01 | 261.56 | A02 | 3 | |
A01 | -71.55 | |||
A01 | 158.48 | |||
A01 | -36.53 | |||
A01 | 245.53 | |||
A01 | 45.53 | |||
A01 | 173.93 | |||
A01 | 229.92 | |||
A01 | 193.14 | |||
A01 | 298.86 | |||
A01 | 298.86 | |||
A02 | 299.84 | |||
A02 | -209.84 | |||
A02 | 299.2 | |||
A02 | 299.2 | |||
A02 | -4000.02 | |||
A02 | -1472.55 | |||
A02 | 408.55 | |||
A02 | -47.05 | |||
A02 | 205.94 | |||
data have;
input ID $ Sal;
datalines;
A01 261.56
A01 261.56
A01 -71.55
A01 158.48
A01 -36.53
A01 245.53
A01 45.53
A01 173.93
A01 229.92
A01 193.14
A01 298.86
A01 298.86
A02 299.84
A02 -209.84
A02 299.2
A02 299.2
A02 -4000.02
A02 -1472.55
A02 408.55
A02 -47.05
A02 205.94
;
data want;
Count=0;
do until (last.ID);
set have;
by ID;
lagSal=lag1(Sal);
if (lagSal>0 & sal<0) & not first.ID then Count+1;
end;
keep id Count;
run;
Hi Draycut,
Thanks so much for the above.
Now if I need to find out average number of times ID went from negative to positive for sal across all ID's then how do I do it
No problem 🙂
You can do like this
data want;
Count=0;
do _N_=1 by 1 until (last.ID);
set have;
by ID;
lagSal=lag1(Sal);
if (lagSal>0 & sal<0) & not first.ID then Count+1;
end;
Avg=Count/_N_;
keep id Count Avg;
format Avg 8.2;
run;
EDIT: Come to think of it, I think you want the overall average. You can do like this then
data want;
set have nobs=nobs end=eof;
if _N_=1 then Count=1;
lagSal=lag1(Sal);
if (lagSal>0 & sal<0) & not first.ID then Count+1;
if eof then do;
Avg=Count/nobs;
output;
end;
keep Avg;
run;
Hi Draycut,
If last two sal are negative then it is counting it two times than for example in the below example last two sal are negative so instead of 3 count is coming as 4 for this ID
ID | SAL |
AD04 | -286.32 |
AD04 | -286.32 |
AD04 | -165.21 |
AD04 | -98.37 |
AD04 | -78.37 |
AD04 | -69.17 |
AD04 | -69.17 |
AD04 | 97.83 |
AD04 | 138.75 |
AD04 | 133.25 |
AD04 | 179.31 |
AD04 | 179.31 |
AD04 | 201.36 |
AD04 | 211.36 |
AD04 | 211.36 |
AD04 | 211.36 |
AD04 | -88.64 |
AD04 | 125.97 |
AD04 | 183.47 |
AD04 | -1674.9 |
AD04 | -1169.32 |
@MV04, I don't see that happening? For demonstration, I all observations in the below so you can see when the counter adds 1?
i only see two instances of a positive becoming negative?
data have;
input ID $ Sal;
datalines;
AD04 -286.32
AD04 -286.32
AD04 -165.21
AD04 -98.37
AD04 -78.37
AD04 -69.17
AD04 -69.17
AD04 97.83
AD04 138.75
AD04 133.25
AD04 179.31
AD04 179.31
AD04 201.36
AD04 211.36
AD04 211.36
AD04 211.36
AD04 -88.64
AD04 125.97
AD04 183.47
AD04 -1674.9
AD04 -1169.32
;
data want;
Count=0;
do until (last.ID);
set have;
by ID;
lagSal=lag1(Sal);
if (lagSal>0 & sal<0) & not first.ID then Count+1;
output;
end;
*keep id Count;
run;
This prints
No. If you want to do that, then do something like this
data want;
Count=0;
do _N_=1 by 1 until (last.ID);
set have;
by ID;
lagSal=lag1(Sal);
if _N_=1 and Sal <1 then Count=1;
if (lagSal>0 & sal<0) & not first.ID then Count+1;
output;
end;
*keep id Count;
run;
@MV04 , did you find your answer 🙂
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.