topic Re: Counting observations separated by missing values in SAS Procedures

Counting observations separated by missing values

SASROCKS — Sat, 15 Sep 2012 04:50:33 GMT

Hi folks,

How do I obtain column b?

a	b
3	1
65	2
2	3
1	4
4	5
.	.
5	1
.	.
6	1
2	2
1	3
2	4
.	.
1	1
.	.
2	1
5	2
.	.
9	1
63	2
2	3
.	.
.	.
1	1
2	2
23	3
6	4
9	5
5	6
2	7
1	8

Basically I would like b to restart counting every time it sees a missing value. My apologies if this technique is too simple for some of you. Thanks in advance.

Cheers

Re: Counting observations separated by missing values

MikeZdeb — Sat, 15 Sep 2012 15:03:41 GMT

hi ... how about this ...

data want;

set have;

b+1;

if missing(a) then b = .;

run;

Re: Counting observations separated by missing values

SASROCKS — Sun, 16 Sep 2012 00:24:54 GMT

Hi Mike,

It totally worked - can't believe it was this simple. Thanks heaps dude!

Re: Counting observations separated by missing values

robertrao — Tue, 18 Sep 2012 19:50:04 GMT

Mike,

Could you explain it to me once again.

I dint understand the question first.

he wants it to start counting from first when it encounters a missing value for b. whay not a?

Also whereever b has a missing value a also has a missing.

What if a doesnt have a missing value...?

When it comes to the answer you posted:

could you explain the code b+1;

also if missing(a) then b=.; (already in the data whereever a is missing b also is missing....i dint understand the logic???

Thanks in advance

Re: Counting observations separated by missing values

MikeZdeb — Tue, 18 Sep 2012 20:10:35 GMT

hi ... the person who posted the question has a data set that has ONLY the variable A ...

data have;

input a;

datalines;

;

so, the code I posted reads that data set and create a new variable named B

at each pass through the data step, 1 is added to the variable B

if a missing value of A is encountered, B is set to MISSING and the count starts over again from 1 as soon as the next non-missing value of A is encountered

the statement B+1; is a SUM statement ...

http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a000289454.htm

and it has some nice features: the value of B is automatically RETAINED (NO RETAIN statement is needed to prevent B from being set to MISSING on each pass through the data step); the initial value of the variable in the SUM statement is ZERO, not missing; even if the value of B is missing, the statement still works

* here's what I posted;

data want;

set have;

b+1;

if missing(a) then b = .;

run;

* it is equivalent to this;

data want;

retain b 0;

set have;

b = sum(b,1);

if missing(a) then b = .;

run;

does that help you understand?

Re: Counting observations separated by missing values

robertrao — Tue, 18 Sep 2012 20:49:45 GMT

hI mIKE ,

i UNDERSTOOD IT BUT I HAVE A SMALL QUESTION.

wHEN EVER a is missing.then b is also set to missing...

and b+1 should have been .+1=. (a dot for the next value of b)!!!!!how did u get 1 in that place?????

Regards

Re: Counting observations separated by missing values

MikeZdeb — Tue, 18 Sep 2012 21:07:06 GMT

hi ...

B+1;

is the same as

B = SUM(B,1);

both of those statement produce a 1 as the result (a SUM statement works like a SUM function and IGNORES missing values), take a look, if I run the SAS job ...

data _null_;

b = .;

b + 1;

put "SUM STATEMENT RESULTS GIVEN B IS MISSING ---> " b;

b = .;

b = sum(b, 1);

put "SUM FUNCTION RESULTS GIVEN B IS MISSING ---> " b;

b = .;

b = b + 1;

put "ASSIGNMENT STATEMENT RESULTS GIVEN B IS MISSING ---> " b;

run;

I get this in the LOG ...

SUM STATEMENT RESULTS GIVEN B IS MISSING ---> 1

SUM FUNCTION RESULTS GIVEN B IS MISSING ---> 1

ASSIGNMENT STATEMENT RESULTS GIVEN B IS MISSING ---> .

Re: Counting observations separated by missing values

robertrao — Tue, 18 Sep 2012 21:14:10 GMT

Thanks Mike fo ryour time;

I was reading the link about sum function i the mean while.........

I got the key point :

IN THE EXPRESSION OF A SUM STATEMENT "SAS TREATS AN EXPRESSION THAT PRODUCES A MISSING VALUE AS ZERO"

THAT IS WHY IT IS BECOMING 0+1 instead of . (missing)+1

Isnt my understanding right???

Also I will try to run the code you just sent me...

Thanks a ton

Re: Counting observations separated by missing values

MikeZdeb — Tue, 18 Sep 2012 21:55:54 GMT

hi ... you're welcome and your interpretation is correct

ps if you are learning about various things is SAS, all the notes I wrote for my course are free (along with lots of other stuff) at ...

EPI 514 (COMPUTER APPLICATIONS IN EPIDEMIOLOGY a/k/a INTRODUCTION TO SAS)

a	b
3	1
65	2
2	3
1	4
4	5
.	.
5	1
.	.
6	1
2	2
1	3
2	4
.	.
1	1
.	.
2	1
5	2
.	.
9	1
63	2
2	3
.	.
.	.
1	1
2	2
23	3
6	4
9	5
5	6
2	7
1	8

a	b
3	1
65	2
2	3
1	4
4	5
.	.
5	1
.	.
6	1
2	2
1	3
2	4
.	.
1	1
.	.
2	1
5	2
.	.
9	1
63	2
2	3
.	.
.	.
1	1
2	2
23	3
6	4
9	5
5	6
2	7
1	8

a	b
3	1
65	2
2	3
1	4
4	5
.	.
5	1
.	.
6	1
2	2
1	3
2	4
.	.
1	1
.	.
2	1
5	2
.	.
9	1
63	2
2	3
.	.
.	.
1	1
2	2
23	3
6	4
9	5
5	6
2	7
1	8