topic How do I select the first non-zero variable from two sequential arrays? in SAS Programming

How do I select the first non-zero variable from two sequential arrays?

Amihic — Mon, 30 Jul 2018 19:09:38 GMT

Hi All,

I would like to select the first non-zero/non-missing variable from the YYEAR variables, and then if no variable is retrieved (aka if the new variable created results in a . (missing)), then select the first non-zero/non-missing variable from the CYEAR variables.

My data set that looks like this:

ID Y1994 Y1996 Y1998 Y2000 Y2002 Y2004 C1988 C1990 C1992 C1994 C1996 C1998 C2000

01 . . . . 12 12 . . . . . . 12

02 . . . . . . . 13 13 . . . .

03 . 14 . . . . . . . 12 . . .

04 0 15 15 . . 15 . . . . . . .

05 . 0 . . . . . . . . . . .

06 . . . . . . . . 11 . . . .

Here is what I want the data set to look like:

ID Y1994 Y1996 Y1998 Y2000 Y2002 Y2004 C1988 C1990 C1992 C1994 C1996 C1998 C2000 a

01 . . . . 12 12 . . . . . . 12 12

02 0 . . . . . . 13 13 . . . . 13

03 . 14 . . . . . . . 12 . . . 14

04 0 15 15 . . 15 . . . . . . . 15

05 . 0 . . . . . . . . . . . .

06 . . . . . . . . 11 . . . . 11

I think I can do something like the following but I do not know how to write the part of the statement that tells the program to cycle through the second array only in the event that a variable is not found after cycling through the first array.

data want ;
set have;
array apples Y: ;
do i=1 to dim(apples) until (a not in (.,0)) ;
a=apples(i);
end;

if a = . then do
array bananas C: ;
do i=1 to dim(bananas) until (a not in (.,0)) ;
a=bananas(i);
end;
run;

Thanks,

AMIHIC

Re: How do I select the first non-zero variable from two sequential arrays?

novinosrin — Mon, 30 Jul 2018 18:15:00 GMT

I don't think you need to iterate all over again, this should do

data want ;
    set have;
    array first Y: ;
         do i=1 to dim(first) until (a not in (.,0)) ;
         a=first(i);
     end;

  if a = . then a=coalesce(of first(*));
run;

Re: How do I select the first non-zero variable from two sequential arrays?

Amihic — Mon, 30 Jul 2018 18:28:44 GMT

Thank you for trying NOVINSRIN, but the code didn't work.

Here's what I wanted

ID Y1994 Y1996 Y1998 Y2000 Y2002 Y2004 C1988 C1990 C1992 C1994 C1996 C1998 C2000 a

01 . . . . 12 12 . . . . . . 12 12

02 0 . . . . . . 13 13 . . . . 13

03 . 14 . . . . . . . 12 . . . 14

04 0 15 15 . . 15 . . . . . . . 15

05 . 0 . . . . . . . . . . . .

06 . . . . . . . . 11 . . . . 11

Here's what I got:

ID Y1994 Y1996 Y1998 Y2000 Y2002 Y2004 C1988 C1990 C1992 C1994 C1996 C1998 C2000 a

01 . . . . 12 12 . . . . . . 12 12

02 0 . . . . . . 13 13 . . . . .

03 . 14 . . . . . . . 12 . . . 14

04 0 15 15 . . 15 . . . . . . . 15

05 . 0 . . . . . . . . . . . .

06 . . . . . . . . 11 . . . . .

The CYEAR variables are not included in the array. It only selects variables starting with the letter Y. So the program never selects from those variables Cyear variables.

Is there a way to address that?

AMIHIC

Re: How do I select the first non-zero variable from two sequential arrays?

FreelanceReinh — Mon, 30 Jul 2018 18:42:50 GMT

Hi @Amihic,

You don't need a second array. Simply define the first like

array first Y: C:;

(Please note that your displayed "want" dataset is not consistent with "have" for ID 05, variables Y1998, Y2000.)

Edit: Also, avoid the use of function names (like FIRST) as array names. (See the note about that in the log.)

Re: How do I select the first non-zero variable from two sequential arrays?

Astounding — Mon, 30 Jul 2018 18:34:33 GMT

Easiest might be a wider array:

data want ;
set have;
array first Y: C: ;
do i=1 to dim(first) until (a > . ) ;
if first(i) then a = first(I);
end;
run;

The condition first(I) will be false whenever the array element is either missing or zero.

Re: How do I select the first non-zero variable from two sequential arrays?

Amihic — Mon, 30 Jul 2018 18:57:55 GMT

Thank you FreeLance, I made the updates to the inconsistent values and changed the array names.

Re: How do I select the first non-zero variable from two sequential arrays?

FreelanceReinh — Mon, 30 Jul 2018 19:07:36 GMT

@Amihic wrote:

"I ... changed the array names."

Except in the DIM function arguments.

Re: How do I select the first non-zero variable from two sequential arrays?

Amihic — Mon, 30 Jul 2018 19:10:42 GMT

Changed now. Thanks!