topic Re: coalesce function in SAS Enterprise Guide

coalesce function

alepage — Tue, 04 Apr 2023 14:47:23 GMT

Hello,

When there is a value for cf_client, I would like extRef to be equal to that value otherwise, it should be equal to the value client.

What's wrong in the script below ?

data test;
   input client $ cf_client;
   datalines;
235987 .
236663 .
236900 .
237009 125
237720 .
237744 .
;

Data test2;
set test;
cf2_client=put(cf_client,8.);
extRef=coalescec(cf2_client,client);
run;

Re: coalesce function

PaigeMiller — Tue, 04 Apr 2023 14:57:01 GMT

@alepage wrote:

Hello,

When there is a value for cf_client, I would like extRef to be equal to that value otherwise, it should be equal to the value client.

What's wrong in the script below ?
data test;
   input client $ cf_client;
   datalines;
235987 .
236663 .
236900 .
237009 125
237720 .
237744 .
;

Data test2;
set test;
cf2_client=put(cf_client,8.);
extRef=coalescec(cf2_client,client);
run;

CF2_CLIENT is never missing. CF2_CLIENT is a character variable, and when it has the value '.' this is not a missing value for a character variable. Instead, ' ' (a single blank space) is a missing value for a character variable. So

data test2;
    set test;
    if not missing(cf_client) then cf2_client=put(cf_client,8.);
    extRef=coalescec(cf2_client,client);
run;

Re: coalesce function

alepage — Tue, 04 Apr 2023 15:14:15 GMT

Please note that the second variable cf_client, is numerical, that this variable is empty, except in one place where the value equal 125. Then I convert this variable into a new one cf2_client as string variable. Then I wish to use the coalescec function by comparing two string variable. When the first value is empty it takes the second one and assign it to extRef.

Will it change something in your script ?

Why the coalesce function seems to see something in cf2_client when it is empty ?

Re: coalesce function

Tom — Tue, 04 Apr 2023 15:18:41 GMT

Re-read what @PaigeMiller wrote.

Then answer this question

Does the string ' .' equal the string ' '?

Re: coalesce function

PaigeMiller — Tue, 04 Apr 2023 15:19:01 GMT

@alepage wrote:

Why the coalesce function seems to see something in cf2_client when it is empty ?

I explained this already. CF2_CLIENT is never empty or missing. It contains a dot for most records, this is not a missing value for a character variable.

Re: coalesce function

alepage — Tue, 04 Apr 2023 15:31:22 GMT

data test;
    infile DATALINES MISSOVER;
	input client $ cf_client;
	DATALINES;
235987   
236663  
236900  
237009 125
237720  
237744  
;

data test2;
    set test;
    cf2_client=put(cf_client,8.);
    extRef=coalescec(cf2_client,client);
run;

data test3;
    set test;
    if not missing(cf_client) then cf2_client=put(cf_client,8.);
    extRef=coalescec(cf2_client,client);
run;

Now, I believe that cf_client contains missing data and when cf_client is missing, it can't convert it to an empty string. Is it the case. put(empty var, 8.) is equal to what ?

Re: coalesce function

alepage — Tue, 04 Apr 2023 15:47:08 GMT

Re: coalesce function

Reeza — Tue, 04 Apr 2023 15:56:20 GMT

@alepage wrote:
Now, I believe that cf_client contains missing data and when cf_client is missing, it can't convert it to an empty string. Is it the case. put(empty var, 8.) is equal to what ?

Did you check the data set created and what happened?

Re: coalesce function

Tom — Tue, 04 Apr 2023 16:00:03 GMT

Note that the MISSING option control what character SAS prints for a missing numeric value.

Try running this code with different characters for the MISSING option.

options missing=' ';
data test;
  input num;
  string1 = put(num,8.);
  string2 = coalescec(string1,'default');
cards;
100
.
;

Also notice that in a data step you have to use the coalesceC() function to coalesce character values. If you use coalesce() function instead then SAS will convert try to convert both character values to numeric.

1428  options missing=' ';
1429  data test;
1430    input num;
1431    string1 = put(num,8.);
1432    string2 = coalesce(string1,'default');
1433  cards;

NOTE: Character values have been converted to numeric values at the places given by: (Line):(Column).
      1432:22   1432:30
NOTE: Invalid numeric data, 'default' , at line 1432 column 30.
RULE:       ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9----+----0
1434        100
num=100 string1=100 string2=100 _ERROR_=1 _N_=1
NOTE: Invalid numeric data, 'default' , at line 1432 column 30.
1435        .
num= string1=  string2= _ERROR_=1 _N_=2
NOTE: The data set WORK.TEST has 2 observations and 3 variables.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds

Re: coalesce function

PaigeMiller — Tue, 04 Apr 2023 16:28:28 GMT

@alepage wrote:
data test;
    infile DATALINES MISSOVER;
	input client $ cf_client;
	DATALINES;
235987   
236663  
236900  
237009 125
237720  
237744  
;

data test2;
    set test;
    cf2_client=put(cf_client,8.);
    extRef=coalescec(cf2_client,client);
run;

data test3;
    set test;
    if not missing(cf_client) then cf2_client=put(cf_client,8.);
    extRef=coalescec(cf2_client,client);
run;
Now, I believe that cf_client contains missing data and when cf_client is missing, it can't convert it to an empty string. Is it the case. put(empty var, 8.) is equal to what ?

I find these sentences very confusing. I am not "convert(ing) it to an empty string". I am creating a new variable which has non-missing value when the IF condition is satisfied, and missing value when the IF condition is not satisfied. I have no idea what the part about "put(empty var 8.)" means or how it is relevant.