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.
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 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.
What is you also had this array
array _vr [*] var12-var1;
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).
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;
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.