topic Re: How do I select the kth nonmissing variable or the first nonzero variable in an array? in SAS Programming

How do I select the kth nonmissing variable or the first nonzero variable in an array?

Amihic — Mon, 30 Jul 2018 16:45:16 GMT

Hi everyone,

I have code that selects the first non-missing variable from an array of variables:

DATA want;
set have;
array t(*) YA: ;
a=coalesce(of t(*));
RUN;

How do I adjust this code to instead select the first non-zero (and nonmissing) variable in the array?

Here is an example of my data:

ID Y1994 Y1996 Y1998 Y2000 Y2002 Y2004

01 . . . . 12 12

02 . . . . . .

03 . 14 . . . .

04 0 15 15 . . 15

05 . 0 17 16 . .

Here is what I would like:

ID Y1994 Y1996 Y1998 Y2000 Y2002 Y2004 a

01 . . . . 12 12 12

02 . . . . . . .

03 . 14 . . . . 14

04 0 15 15 . . 15 15

05 . 0 17 16 . . 17

Thanks,

AMIHIC

Re: How do I select the kth nonmissing variable or the first nonzero variable in an array?

RW9 — Mon, 30 Jul 2018 16:30:05 GMT

Correct, coalesce takes the first non-missing, so would not work. You could do it a number of ways depending on what your data looks like - which you have not provided:

You could concatenate all the array into a string, then compress out missings.

You could loop over the array and find the Nth non missing, something like:

%let n=4;
data want;
  set have;
  array t{*} yafs:;
  n=0;
  do i=1 to dim(t);
    if t{i} ne . then n=sum(n,1);
    if n=&n. then result=n;
  end;
run;

Re: How do I select the kth nonmissing variable or the first nonzero variable in an array?

Amihic — Mon, 30 Jul 2018 16:48:46 GMT

Hi RW9,

I edited my original post to include a sample of my data.

AMIHIC

Re: How do I select the kth nonmissing variable or the first nonzero variable in an array?

Tom — Mon, 30 Jul 2018 16:53:10 GMT

Use an ARRAY and a DO loop.

data have ;
  input 
ID  $ Y1994  Y1996  Y1998  Y2000 Y2002 Y2004 ;
cards;
01      .            .            .           .          12        12
02      .            .            .           .            .           . 
03      .            14         .            .           .           .
04      0           15         15         .           .         15
05       .            0          17         16         .          .
;

data want ;
 set have ;
 array y y:;
 do i=1 to dim(y) until (a not in (.,0)) ;
   a=y(i);
 end;
run;
proc print;
run;