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
;
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;
What if have is a non-missing value other than 99?
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;
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!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.