catx function where columns are null

Question

I am working with catx function in sas enterprise guide. When I want to concatenate 2 columns I am using

catx('-',column1,column2)

It works fine but when both column1 and column2 are null it still gives '-' as output

How to restrict this, if both are null just give me null

Do you also what it to add the hyphen when only one of the values is missing? — Tom, Jul 26 '23 at 14:57
What do the values show when you format the columns with $HEX32. ? If it's not 2020202020... then you have control characters in your values. — Richard, Jul 26 '23 at 17:59

score 2 · Answer 1 · answered Jul 26 '23 at 17:57

CATX will return a null (blank) value if all concatenation values are blank.

If your result is appearing as - then your values probably are an invisible non-printable character such as tab, newline or carriage return.

Look ma, no dash!


data have;
s=' ';
t=' ';
u = catx('-',s,t);
run;

proc sql;
  create table result as select s,t,catx('-',s,t) as u from have;
quit;

score 1 · Answer 2 · answered Jul 26 '23 at 14:36

Try the following :

/* Using PROC SQL */
proc sql;
    SELECT 
        CASE 
            WHEN column1 IS NOT NULL OR column2 IS NOT NULL THEN catx('-', column1, column2)
            ELSE NULL
        END AS concatenated_columns
    FROM your_table;
quit;

catx function where columns are null

2 Answers2