topic How to pull last non-missing variable from a series of variables? in SAS Data Management

How to pull last non-missing variable from a series of variables?

JackHP — Fri, 20 Dec 2019 15:53:37 GMT

Hello,

I would like to create a variable, "LastStress", that would pull the last non-missing observation from a series of variables that have the same prefix and different suffixes that correlate to visits (my data is in a "wide" format).

Example

Have:

ID Stress1 Stress5 Stress10 Stress15 Stress20

1 10 8 . 3 .

2 3 2 . . .

3 15 . . . 10

Want:

ID Stress1 Stress5 Stress10 Stress15 Stress20 LastStress

1 10 8 . 3 . 3

2 3 2 . . . 2

3 15 . . . 10 10

Any help on where to start with the initial code would be very much appreciated. Thank you!

Re: How to pull last non-missing variable from a series of variables?

PaigeMiller — Fri, 20 Dec 2019 16:00:58 GMT

/* UNTESTED CODE */
data want;
    set have;
    array stress stress1 stress5 stress10 stress15 stress20;
    do i=1 to dim(stress);
        if not missing(stress(i)) then laststress=stress(i);
    end;
    drop i;
run;

Re: How to pull last non-missing variable from a series of variables?

novinosrin — Fri, 20 Dec 2019 16:21:19 GMT

HI @JackHP You just need to reverse the LOOP and exit



data have;
input ID Stress1 Stress5 Stress10 Stress15 Stress20;
cards;
1     10           8            .              3             .
2      3            2            .              .              .
3      15           .            .              .             10
;

data want;
set have;
array t stress1--stress20;
do _n_=dim(t) to 1 by -1;
 if not missing(t(_n_)) then do;
  laststress=t(_n_);
  leave;
  end;
end;
run;

And a little terser

data want;
set have;
array t stress1--stress20;
do _n_=dim(t) to 1 by -1 until(t(_n_)>.);
  laststress=t(_n_);
end;
run;

Re: How to pull last non-missing variable from a series of variables?

ed_sas_member — Fri, 20 Dec 2019 18:17:40 GMT

Hi @JackHP

You can try this code:

/* Retrieve the list of variables with the prefix "Stress" */
proc contents data=have out=list_var (keep = name where=(lowcase(name) like 'stress%')) noprint;
run;

proc sql noprint;
	select name into:list_var_stress separated by " " from list_var;
run;

/* Compute Laststress */
data want;
    set have;
    array stress (*) &list_var_stress;
    do i=1 to dim(stress);
        if stress(i) ne . then laststress=stress(i);
    end;
    drop i;
run;

Re: How to pull last non-missing variable from a series of variables?

mkeintz — Sat, 21 Dec 2019 02:11:18 GMT

I would suggest avoiding constructing and traversing a loop altogether. This task is what the COALESCE function was made for:

data have;
input ID Stress1 Stress5 Stress10 Stress15 Stress20;
cards;
1     10           8            .              3             .
2      3            2            .              .              .
3      15           .            .              .             10
;
data want;
   set have;
   laststress=coalesce(stress20,stress15,stress10,stress5,stress1);
run;

Of course, the above assume you know in advance the names of the individual stress variables. If you don't know those names, or just want to generalize your solution, you can take a dip in the metadata via proc sql/dictionary, and make a macrovar (reverse_list below) listing the variables in reverse positional order:

proc sql noprint;
  select name into: reverse_list separated by ','
  from dictionary.columns where libname='WORK' and memname='HAVE'
  and upcase(name) like 'STRESS%' 
  order by varnum descending;
quit;
%put &=reverse_list;
data want;
  set have;
  laststress=coalesce(&reverse_list);
run;

Re: How to pull last non-missing variable from a series of variables?

hashman — Sat, 21 Dec 2019 23:59:41 GMT

@JackHP:

If you don't mind listing all the stress variables or a two-step process, I'd defer to @mkeintz's offer. Otherwise, you can try:

data have ;                                                                                                                                                                                                                                                     
  input id stress1 stress5 stress10 stress15 stress20 ;                                                                                                                                                                                                         
  cards ;                                                                                                                                                                                                                                                       
1  10   8  .  3   .                                                                                                                                                                                                                                             
2   3   2  .  .   .                                                                                                                                                                                                                                             
3  15   .  .  .  10                                                                                                                                                                                                                                             
;                                                                                                                                                                                                                                                               
run ;                                                                                                                                                                                                                                                           
                                                                                                                                                                                                                                                                
data want ;                                                                                                                                                                                                                                                     
  set have ;                                                                                                                                                                                                                                                    
  laststress = input (scan (catx (".", of stress:), -1), f.) ;                                                                                                                                                                                                  
run ;

The idea is that if you delimit the values with a period, it becomes indistinguishable from the period representing a missing value after the implicit num-to-char conversion by CATX.

Kind regards

Paul D.

Re: How to pull last non-missing variable from a series of variables?

JackHP — Fri, 24 Jan 2020 17:43:04 GMT

Thank you for sharing the coalesce function - this worked perfectly.

I used this to construct a macro as I wanted to repeat this with a series of variables (code below for anyone interested). Thank you!

%Macro lastvar(var);
data mvrx19.master;
set mvrx19.master;
last_&Var=coalesce(&var._20,&var._15,&var._10,&var._5,&var._1);
run;
%mend lastvar;

%lastvar(PSSNS);
%lastvar(PSS10NS);
%lastvar(PSS4NS);
%lastvar(dietfruit);
%lastvar(dietvet);
%lastvar(FI);

%Macro Charlastvar(var);
data mvrx19.master;
set mvrx19.master;
last_&Var=coalescec(&var._20,&var._15,&var._10,&var._5,&var._1);
run;%mend Charlastvar;

%Charlastvar(Physhealth);
%Charlastvar(MFA);