Alternative function for extract non missing value from two column

[dash]

Hi,

I want to find non missing value between col id1 and id2 and then place it in variable x like below.

Is there any alternative function to solve this if more variables are there.

data new;
input id1 id2 $;
cards;
1 11
2 .
. 3
. .
. abc
7 .
;
run;
data new1;
set new;
if id1 eq . then x=id2;
else if id1 ne . then x=id1;
else x=.;
run;

[Kurt_Bremser]

Use the COALESCE function.

[ballardw]

---

@dash wrote:

Hi,

I want to find non missing value between col id1 and id2 and then place it in variable x like below.

Is there any alternative function to solve this if more variables are there.

data new;
input id1 id2 $;
cards;
1 11
2 .
. 3
. .
. abc
7 .
;
run;
data new1;
set new;
if id1 eq . then x=id2;
else if id1 ne . then x=id1;
else x=.;
run;

---

So is your X variable supposed to be numeric or character? This is very important since you are starting with one of each. The character value 'abc' cannot be stored in a numeric.

Any time you let the automatic conversion of numeric to character occur you have a chance of getting unexpected results. So consideration of how the create the character version of "id1" needs some consideration.

[dash]

I use the function but why I am not getting correct output. Code for reference.

data new;
input id1 id2 $;
cards;
1 11.7
2 .
. 3-4.5
. .
. abc
7 .
;
run;
data new1;
set new;
id1_=put(id1,8.);
X=coalescec(id1_, id2);
run;

[dash]

Can anyone help me why coalescec function is not working in the newly provided dataset

[ballardw · Accepted solution]

---

@dash wrote:
Can anyone help me why coalescec function is not working in the newly provided dataset

---

You use 

id1_=put(id1,8.);

unconditionally. So the value of id1_ is "       .", 7 blanks and a dot by default when id1 is missing. So there is ALWAYS a value for Id_1 and that is the result.

Perhaps:

data new1;
   set new;
   if not missing(id1) then id1_=put(id1,8. -L);
   X=coalescec(id1_, id2);
run;

Best practice would be to specify a length for X before first use, otherwise length may not hold all the values you need.

The -L in the Put for Id1 results in left justified text, which may be nicer for output when the result is the Id1_ value.

[Reeza]

coalesceC()

and/or CATT() functions will do what you need.

Note that coalesceC (added C) has to be used with character variables.

---

@dash wrote:

Hi,

I want to find non missing value between col id1 and id2 and then place it in variable x like below.

Is there any alternative function to solve this if more variables are there.

data new;
input id1 id2 $;
cards;
1 11
2 .
. 3
. .
. abc
7 .
;
run;
data new1;
set new;
if id1 eq . then x=id2;
else if id1 ne . then x=id1;
else x=.;
run;

---