topic Re: Alternative function for extract non missing value from two column in SAS Programming

Alternative function for extract non missing value from two column

dash — Mon, 19 Oct 2020 15:09:21 GMT

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;

Re: Alternative function for extract non missing value from two column

Kurt_Bremser — Mon, 19 Oct 2020 15:13:06 GMT

Use the COALESCE function.

Re: Alternative function for extract non missing value from two column

ballardw — Mon, 19 Oct 2020 15:32:56 GMT

@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.

Re: Alternative function for extract non missing value from two column

Reeza — Mon, 19 Oct 2020 15:34:24 GMT

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;

Re: Alternative function for extract non missing value from two column

dash — Mon, 19 Oct 2020 15:37:28 GMT

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;

Re: Alternative function for extract non missing value from two column

dash — Mon, 19 Oct 2020 16:47:56 GMT

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

Re: Alternative function for extract non missing value from two column

ballardw — Mon, 19 Oct 2020 16:56:09 GMT

@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.