topic Re: How can I get the first and the last MISSING value from a particular ROW in SAS Programming

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

ramgouveia — Tue, 02 Nov 2021 15:55:06 GMT

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.

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

maguiremq — Tue, 02 Nov 2021 16:54:33 GMT

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.

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

ballardw — Tue, 02 Nov 2021 17:16:42 GMT

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).

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

data_null__ — Tue, 02 Nov 2021 18:50:15 GMT

What is you also had this array

array _vr [*] var12-var1;

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

Ksharp — Wed, 03 Nov 2021 12:01:57 GMT

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;