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