topic Re: how to find out the last non missing value in a list of variable in sas in SAS Programming

how to find out the last non missing value in a list of variable in sas

vThanu — Mon, 04 Mar 2019 11:01:27 GMT

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

Re: how to find out the last non missing value in a list of variable in sas

DanielLangley — Mon, 04 Mar 2019 11:22:09 GMT

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.

Re: how to find out the last non missing value in a list of variable in sas

RW9 — Mon, 04 Mar 2019 11:37:34 GMT

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;

Re: how to find out the last non missing value in a list of variable in sas

vThanu — Mon, 04 Mar 2019 12:30:38 GMT

Thanks for your support....

Re: how to find out the last non missing value in a list of variable in sas

mansour_ib_sas — Mon, 04 Mar 2019 12:51:25 GMT

data want;
set have;
NM= coalesce(d,c,b,a);
run;

Re: how to find out the last non missing value in a list of variable in sas

DanielLangley — Mon, 04 Mar 2019 13:00:04 GMT

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;

Re: how to find out the last non missing value in a list of variable in sas

RW9 — Mon, 04 Mar 2019 13:06:39 GMT

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.

Re: how to find out the last non missing value in a list of variable in sas

RW9 — Mon, 04 Mar 2019 13:09:42 GMT

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.

Re: how to find out the last non missing value in a list of variable in sas

novinosrin — Mon, 04 Mar 2019 14:27:53 GMT

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

Re: how to find out the last non missing value in a list of variable in sas

mansour_ib_sas — Tue, 05 Mar 2019 13:34:10 GMT

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;