topic Assign values to missing values in between in Statistical Procedures

Assign values to missing values in between

bharath86 — Wed, 28 Jul 2021 11:37:17 GMT

This is desired output I am looking for, in the previous code this is assigning value 999 at all blank records, but I am looking for 999 on those inbetween blank spaces where there is any value before and after the blank records.

Ex: ID=2, section=c,f,g.

But this code is assigning values 999 at all blank records. Please suggest something else.

ID	Sequence	section	value	desired
1	1	a	0	0
1	2	b	.	.
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
2	6	f	.	999
2	7	g	.	999
2	8	h	0	0
3	1	a	.	.
3	2	b	1	1
4	1	a	0	0
4	2	b	.	.

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;
 do until(last.value);
  set have;
  by id value notsorted;
  if first.id then first=1;
  if last.id then last=1;
 end;

  do until(last.value);
  set have;
  by id value notsorted;
  desired=value;
  if not first and not last and missing(value) then desired=999;
  output;
 end;
 drop first last;
 run;

Re: Assign values to missing values in between

Kurt_Bremser — Wed, 28 Jul 2021 11:42:01 GMT

Try this:

data want;
counter = 0;
do until (last.id);
  set have;
  by id;
  counter + 1;
  if value ne .
  then do;
    lastcount = counter;
    if firstcount = . then firstcount = counter;
  end;
end;
counter = 0;
do until (last.id);
  set have;
  by id;
  counter + 1;
  if value = . and counter gt firstcount and counter lt lastcount
  then desired = 999;
  else desired = value;
  output;
end;
drop counter firstcount lastcount;
run;

Re: Assign values to missing values in between

PaigeMiller — Wed, 28 Jul 2021 13:41:00 GMT

May I suggest that instead of assigning a number to these, you assign a missing value that has a specific meaning, such as .A and so now the other missings can be distinguished from the .A

If you assign a number, then any further analysis or reporting (such as computing means or a percent) has to specially handle that number differently than the other numbers; not so if you use .A

Re: Assign values to missing values in between

Ksharp — Wed, 28 Jul 2021 11:59:33 GMT

I don't see anything wrong. After running my code. I got this :

where is wrong ?

Re: Assign values to missing values in between

Kurt_Bremser — Wed, 28 Jul 2021 12:02:03 GMT

PS If you want to treat zero as a "blank value", you need to include that in the conditions.