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
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.
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;
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
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.)
Thank you FreeLance, I made the updates to the inconsistent values and changed the array names.
@Amihic wrote:
"I ... changed the array names."
Except in the DIM function arguments.
Changed now. Thanks!
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.
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.