topic Assign a value based on missing and nonmissing rows in SAS Programming

Assign a value based on missing and nonmissing rows

bharath86 — Wed, 16 Jun 2021 10:45:27 GMT

Hi,

I have a question on how to assign 999 in the column 'desired', if the cell in 'value' column is missing and rows before and after the missing cell has some value (0 or 1 or 2) then we should assign 999 under desired column in that row.

999 must be assigned based on missing values under each ID and section. Please advise.

ID	Sequence	section	value	desired
1	1	a	0	0
1	2	b		999
1	3	c	1	1
1	4	d	0	0
1	5	e	0	0
2	1	a	1	1
2	2	b	1	1
2	3	c		999
2	4	d	2	2
2	5	e	2	2

Thank you

Re: Assign a value based on missing and nonmissing rows

PeterClemmensen — Wed, 16 Jun 2021 10:47:19 GMT

What if the value column has another value, say 3 for some ID?

Re: Assign a value based on missing and nonmissing rows

bharath86 — Wed, 16 Jun 2021 10:52:39 GMT

There is no chance for another value as it is predefined to have either 0 or 1 or 2 in this case.

Re: Assign a value based on missing and nonmissing rows

PeterClemmensen — Wed, 16 Jun 2021 11:00:13 GMT

Ok, so that part really doesn't matter or?

So you simply want to set desired = 999 if value is missing, correct?

Re: Assign a value based on missing and nonmissing rows

Kurt_Bremser — Wed, 16 Jun 2021 11:01:19 GMT

What if the missing value appears as first or last for a given ID?

Re: Assign a value based on missing and nonmissing rows

bharath86 — Wed, 16 Jun 2021 11:07:53 GMT

I want to assign 999 if the rows before and after the missing rows has any value (0 or 1 or 2).

For each ID.

Re: Assign a value based on missing and nonmissing rows

bharath86 — Wed, 16 Jun 2021 11:05:55 GMT

Then we can ignore them. It is those missing values inbetween the rows with any value (0 or 1 or 2) must be assigned as 999.

If first or last is blank then we should ignore them.

Re: Assign a value based on missing and nonmissing rows

Kurt_Bremser — Wed, 16 Jun 2021 11:21:05 GMT

Run this:

data have;
infile datalines dsd dlm=" " truncover;
input ID $ Sequence section $ value;
datalines;
1 1 a 0
1 2 b  
1 3 c 1
1 4 d 0
1 5 e 0
2 1 a 1
2 2 b 1
2 3 c  
2 4 d 2
2 5 e 2
3 1 a .
3 2 b 1
4 1 a 0
4 2 b
;

data want;
set have;
by id;
set /* create a "look-ahead" */
  have (
    firstobs=2
    keep=id value
    rename=(id=_id value=_value)
  )
  have ( /* this is needed to prevent a premature end of the data step */
    obs=1
    keep=id value
    rename=(id=_id value=_value)
  )
;
_lvalue = lag(value);
if
  not (first.id or last.id) and
  value = . and _lvalue ne . and _value ne .
then desired = 999;
else desired = value;
drop _:;
run;

Note how I create the dataset with a data step with DATALINES; by doing this in the future, you will make it MUCH easier for us to help you, as we will know exactly what we have to deal with.

I added two groups that illustrate the first/last behavior.

Re: Assign a value based on missing and nonmissing rows

Ksharp — Wed, 16 Jun 2021 11:45:07 GMT

Same as Kurt's code :

data have;
infile datalines dsd dlm=" " truncover;
input ID $ Sequence section $ value;
datalines;
1 1 a 0
1 2 b  
1 3 c 1
1 4 d 0
1 5 e 0
2 1 a 1
2 2 b 1
2 3 c  
2 4 d 2
2 5 e 2
3 1 a .
3 2 b 1
4 1 a 0
4 2 b
;

data want;
 merge have have(firstobs=2 keep=id value rename=(id=_id value=_value));
 desired=value;
 if missing(value) and 
    (id=lag(id) and not missing(lag(value))) and
    (id=_id and not missing(_value)) then desired=999;
drop _: ;
run;

Re: Assign a value based on missing and nonmissing rows

bharath86 — Thu, 17 Jun 2021 08:19:57 GMT

Hi @Kurt_Bremser

Please see this example. I have inserted 2 additional blank rows under ID=2. This fails when it has 2 or more consequtive blank rows.

I tried modifying firstobs value but it didnt work for second consequtinve blank value.

data have;
infile datalines dsd dlm=" " truncover;
input ID $ Sequence section $ value;
datalines;
1 1 a 0
1 2 b  
1 3 c 1
1 4 d 0
1 5 e 0
2 1 a 1
2 2 b 1
2 3 c  
2 4 d 2
2 5 e 2
2 6 f  
2 7 g  
2 8 h 0
3 1 a .
3 2 b 1
4 1 a 0
4 2 b
;
run;



data want;
set have;
by id;
set /* create a "look-ahead" */
  have (
    firstobs=2
    keep=id value
    rename=(id=_id value=_value)
  )
  have ( /* this is needed to prevent a premature end of the data step */
    obs=1
    keep=id value
    rename=(id=_id value=_value)
  )
;
_lvalue = lag(value);
if
  not (first.id or last.id) and
  value = . and _lvalue ne . and _value ne .
then desired = 999;
else desired = value;
drop _:;
run;