data have;
input a b c d;
cards;
1 2 . 4
4 . 3 6
2 3 5 .
. 2 3 5
;
I want to find out the last non-missing value?
Generally if we use coalesce function we get first non-missing value right.
I am looking for last non-missing value.... in the same sequencial order (a,b.c.d...) or (A--D);
Thanks in advance
data have;
input a b c d;
cards;
1 2 . 4
4 . 3 6
2 3 5 .
. 2 3 5
;
run;
data want(drop = i);
set have;
array nums[*] A--D;
do i = dim(nums) to 1 by -1 until (i=1 or vvalue(nums[i]) ne . );
LastNonMissing = nums[i];
end;
run;
LastNonMissing will be missing if the entire row is missing.
Thanks for your support....
As an alternative to the array method:
data have; input a b c d; cards; 1 2 . 4 4 . 3 6 2 3 5 . . 2 3 5 ; run; data want; set have; lastmissing=scan(catx(',',of a--d),-1,','); run;
data want;
set have;
NM= coalesce(d,c,b,a);
run;
Yes, the coalesce is a good idea, only problem is you can't do lists in reverse. It would be great to do:
lastmissing=coalesce(of d--a);
But we can't as that's not the order of the variables. So needing to put a lot of variables in the line could get long winded.
Hi @RW9 This is a really elegant solution however sometimes numbers contain commas which could throw your results off.
data have;
format a b c d comma9.;
input a b c d;
cards;
1 2 . 4
4 . 3 6
2 3 5 .
. 2 3 5
4 . 3000 .
;
run;
data want(drop = i);
set have;
array nums[*] A--D;
do i = dim(nums) to 1 by -1 until (i=1 or nums[i] ne . ); *Modified to get rid of vvalue;
LastNonMissing = nums[i];
end;
lastmissing=scan(catx(',',of a--d),-1,','); *from RW9;
NM= coalesce(d,c,b,a); * from mansour_ib_sas;
run;
True (although whether actual numbers (rather than formatted) can contain commas is a different discussion ) and hadn't though about that. Just replace the comma usage:
data want; set have; lastmissing=scan(catx('|',of a--d),-1,'|'); run;
A bar (pipe) for instance.
Hi @vThanu
Thank you for the interesting question.
The solutions you received so far:
1. Loop aka linear search
2. Colaesce ( problem: what if you have numerous variables to reverse the list, this would involve loading the list in a macro var in a reverse order
3. scan ( didn;t work as it didn;t produce 4 6 5 5 as the wanted result but I think this is a slick approach regardless)
One easy approach in my opinion is
data have;
input a b c d;
cards;
1 2 . 4
4 . 3 6
2 3 5 .
. 2 3 5
;
run;
data want;
set have;
temp=compress(cats(of a--d),'.');
lastnonmissing=char(temp,length(temp));
drop temp;
run;
This handles the variable list conveniently, stripping of '.' from the last and extracting the last one.
Results:
a b c d lastnonmissing 1 2 . 4 4 4 . 3 6 6 2 3 5 . 5 . 2 3 5 5
Another solution is possible:
data test;
input a b c d;
cards;
1 2 . 4
4 . 3 6
2 3 5 .
. 2 . .
;
run;
proc contents noprint data=test out=test1;run;
proc sql noprint ;
select name into : col separated by ' ' from test1 order by varnum desc;
quit;
data test1(drop=i);
set test;
array nums &col.;
do i=dim(nums) to 1 by -1;
if nums(i) ^= . then do;
lnm=nums(i);
output;
leave;
end;
end;
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.