how to stop do loop on a condition

[MV04]

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

[PeterClemmensen]

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;

[MV04]

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

[PeterClemmensen]

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;

[MV04]

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

[PeterClemmensen]

@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 

[MV04]

Hi Draycut Are you not counting the first negative sal ?

[PeterClemmensen]

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;

[PeterClemmensen]

@MV04 , did you find your answer 🙂