How to write an if/else statement based on a conditional event

[N8]

Hi everyone,

I'm having difficulty coming up with the correct logic coding to set a conditional if/else statement that is referencing a range of values as its reference. I'll provide a dataline with desired output below. The field district is my grouping value.

By district, I would like the following 3 unique values to populate the want column 

1. If Have = 99 for all five years then Want = 1 

2. If Have = . before it equaled 99, then Want = 2 ;

3. If Have = . after Have = 99, then Want = 3 ;

It's the pre/post logic that i'm struggling with as there are instances where the event occurred after the start date, but did not continued until the end date. Thanks everyone! I appreciate the help, nate

data Example;
infile datalines ;
input district year have want;
datalines;

1 2006 99 1
1 2007 99 1
1 2008 99 1
1 2009 99 1
1 2010 99 1
2 2006 . 2
2 2007 . 2
2 2008 99 1
2 2009 99 1
2 2010 . 3
3 2006 99 1
3 2007 99 1
3 2008 99 1
3 2009 99 1
3 2010 . 3
4 2006 . 2
4 2007 . 2
4 2008 99 1
4 2009 99 1
4 2010 99 1
;

[Kurt_Bremser]

What if have is a non-missing value other than 99?

[N8]

Hi Kurt - the Have column will always be populated with non-unique value. It could be alpha or numeric, but it will be the same except for years where the event did not occur

[MINX · Accepted solution]

Your logic is not clear in your post. My understanding for your logic is:

1. if have=99 then want=1

2. if have is missing and there is have=99 after this missing in each district then want=2;

3. if have is missing and there is have=99 before this missing in each district then want=3;

If my understanding is correct, my codes might work. Here, variable want1 should be equal to your expected value as list in want.

proc sort data=Example;
	by district year;
run;

data want;
	set Example;
	by district year;
	retain accum 0;
	if first.district then accum = 0;
	if have = 99 then i = 1;
	accum = sum(accum,i);
	if accum=0 then want1 = 2;		/* Setup 2 for leading missing per district */
	else if i = 1 then want1 = 1;	/* Setup 1 for have=99 per district */
	else if i = . then want1 =3;	/* Setup 3 for tailing missing per district */
run;

[N8]

Thanks Minx. I appreciate your help and for letting me know a different way to write the logic steps. Thanks again! Nate