CATX with List Containing Missing Variables

[tburus]

I am trying to use CATX() to concatenate lists of character variables of within a dataset, each of which contains a different number of missing variables on the right end. I am getting them concatenated successfully, but all of the lists are ending with , . (the separator and then a dot). I am trying to get rid of this, but can't seem to figure out a way.

Code:

data sport_part_report;
	set all_schools_sport_t;
  	sports = catx(", ", OF col1-col14);
  	drop school_id _name_ _label_ col1 col2 col3 col4 col5 col6 col7 col8 col9 col10 col11 col12 col13 col14;
run;

Output example:

BA-M, LA-W, SB-W, .

[Patrick · Accepted solution]

From the looks of it input table all_schools_sport_t gets created via a Proc Transpose and all your COL variables are now character.

Because they are character the dot is an actual value and though gets used by the CATX() function (which omits missing variables).

What should resolve this: Use OPTION MISSING=' '; before your Proc Transpose. This should populate the COL variable with a blank instead of a dot and then the CATX() function will treat it as missing.

[tburus]

Thanks. I received this code from a partner and realized after posting that they had indicated more columns than actually existed. When I removed that, the issue went away. This is helpful to know as well, however.

[mkeintz]

I can not reproduce your problem with trailing missing character variables (a blank for the rightmost variable(s)).  So if CATX is showing a trailing " .", then your rightmost non-blank character variable must have a character "." in it.  Or maybe the rightmost variable is a numeric with a missing value.