topic Re: how to stop do loop on a condition in New SAS User

how to stop do loop on a condition

MV04 — Tue, 11 Jun 2019 08:19:06 GMT

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

Re: how to stop do loop on a condition

PeterClemmensen — Tue, 11 Jun 2019 08:31:42 GMT

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;

Re: how to stop do loop on a condition

MV04 — Tue, 11 Jun 2019 09:12:10 GMT

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

Re: how to stop do loop on a condition

PeterClemmensen — Tue, 11 Jun 2019 09:28:15 GMT

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;

Re: how to stop do loop on a condition

MV04 — Tue, 11 Jun 2019 09:46:21 GMT

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

Re: how to stop do loop on a condition

PeterClemmensen — Tue, 11 Jun 2019 10:09:52 GMT

@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

Re: how to stop do loop on a condition

MV04 — Tue, 11 Jun 2019 10:49:15 GMT

Hi Draycut Are you not counting the first negative sal ?

Re: how to stop do loop on a condition

PeterClemmensen — Tue, 11 Jun 2019 11:43:03 GMT

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;

Re: how to stop do loop on a condition

PeterClemmensen — Wed, 12 Jun 2019 08:50:17 GMT

@MV04 , did you find your answer 🙂