How can I get the first and the last MISSING value from a particular ROW

[ramgouveia]

Hi,

Have the following problem, I want to identify the first and the last missing values in a row. Take as an example the following code:

data example;
  input id var1 var2 var3 var4 var5 var6 var7 var8 var9 var10 var11 var12;
cards;

A	1 2 3 . . . . . 1 1 1 3
B	3 3 2 1 3 2 1 . . . . .
C	. . . . 1 2 3 1 2 3 2 .
D	3 . 1 . 3 . 1 . 3 . 1 .
F	1 3 . . 1 3 . . 1 3 . .
E	3 2 1 . . . . . 1 1 1 3
G	3 3 2 1 3 2 1 . . . . .
H	. . . . . 1 2 3 1 2 3 2
I	3 . 1 . 3 . 1 . 3 . 1 .
J	A E . . A E . . A E . . 
;

In row A the first is var4 and the last var8

In row D the first is var2 and the last is var12

Thank you.

[maguiremq · Accepted solution]

data example;
infile datalines missover;
  input id $ var1 $ var2 $ var3 $ var4 $ var5 $ var6 $ var7 $ var8 $ var9 $ var10 $ var11 $ var12 $;
cards;
A 1 2 3 . . . . . 1 1 1 3
B 3 3 2 1 3 2 1 . . . . .
C . . . . 1 2 3 1 2 3 2 .
D 3 . 1 . 3 . 1 . 3 . 1 .
F 1 3 . . 1 3 . . 1 3 . .
E 3 2 1 . . . . . 1 1 1 3
G 3 3 2 1 3 2 1 . . . . .
H . . . . . 1 2 3 1 2 3 2
I 3 . 1 . 3 . 1 . 3 . 1 .
J A E . . A E . . A E . . 
;

data want (drop = i);
	set example;
	length first last $5.;
	array _v [*] var:;
	first = catt("var", whichc("", of var:));
	do i = 1 to dim(_v);
		if missing(_v[i]) then last = vname(_v[i]);
	end;
run;

id var1 var2 var3 var4 var5 var6 var7 var8 var9 var10 var11 var12 first last 
A 1 2 3           1 1 1 3 var4 var8 
B 3 3 2 1 3 2 1           var8 var12 
C         1 2 3 1 2 3 2   var1 var12 
D 3   1   3   1   3   1   var2 var12 
E 3 2 1           1 1 1 3 var4 var8 
F 1 3     1 3     1 3     var3 var12 
G 3 3 2 1 3 2 1           var8 var12 
H           1 2 3 1 2 3 2 var1 var5 
I 3   1   3   1   3   1   var2 var12 
J A E     A E     A E     var3 var12 

 I should add that this would be much easier if you transposed the data set, but I'm not quite sure what you're actually working with as far as data size.

[data_null__]

What is you also had this array

array _vr [*] var12-var1;

[ballardw]

Some minor details: 

What do you expect to see if there are no missing values?

What do you expect to see if there is exactly one missing value? The "first" would also be the "last" in that case.

Are all of the variables you want to search of the same type, all numeric or all character? If not there is much more work involved. Since your row J shows values of A and E, that the data step provided will make missing this could be pretty important question. (Not to mention that the row identification letters don't make it into the data set either).

[Ksharp]

data example;
infile datalines missover;
  input id $ var1 $ var2 $ var3 $ var4 $ var5 $ var6 $ var7 $ var8 $ var9 $ var10 $ var11 $ var12 $;
cards;
A 1 2 3 . . . . . 1 1 1 3
B 3 3 2 1 3 2 1 . . . . .
C . . . . 1 2 3 1 2 3 2 .
D 3 . 1 . 3 . 1 . 3 . 1 .
F 1 3 . . 1 3 . . 1 3 . .
E 3 2 1 . . . . . 1 1 1 3
G 3 3 2 1 3 2 1 . . . . .
H . . . . . 1 2 3 1 2 3 2
I 3 . 1 . 3 . 1 . 3 . 1 .
J A E . . A E . . A E . . 
;

data want;
 set example;
 array x{*} $ var:;
 do i = 1 to dim(x);
   if missing(x{i}) then do;
        first=vname(x{i});leave;
   end;
 end;

 do i = dim(x) to 1 by -1;
   if missing(x{i}) then do;
        last=vname(x{i});leave;
   end;
 end;
drop i;
run;