topic Re: Concatenate all numeric columns with CATX in SAS Programming

Concatenate all numeric columns with CATX

Mr_Kempe — Wed, 06 Jan 2021 10:47:48 GMT

Hi all,

I would like to concatenate all numeric columns with the CATX function, without specifying all columns names.

For example:

data work.my_ds;
	input char1 $ char2 $ num1 num2;
	datalines;
A Z 111 222
B Y 123 456
C X 987 654
;
run;

My first thought was to use CATX("/", of _numeric_). However, this doesn't work because of the data type of the new variable concat_num

/* Option 1: Doesn't work */
data work.option1;
	set work.my_ds;
	
	concat_num = catx("/", of _numeric_);
run;

The second option is to define the data type of concat_num. This works, but I don't like the solution because you have to define directly the length of the new variable. The defined length might be too short or too long.

/* Option 2: Works, but you need to define the Length of concat_num variable */
data work.option2;
	set work.my_ds;
	
	length concat_num $20;
	concat_num = catx("/", of _numeric_);
run;

The third option is to use the Dictionary tables to create a macro variable of all numeric columns. This option works fine, but might be hard to understand for someone who isn't very experienced in SAS.

/* Option 3: Works, but not very elegant. Hard to understand for less experienced programmer */
proc sql;
	select name into :numeric_vars
	separated by ","
	from dictionary.columns
	where libname="WORK"
	and memname="MY_DS"
	and type="num";
quit;

data work.option3;
	set work.my_ds;
	
	concat_num = catx("/", &numeric_vars.);
run;

Does anyone have a more elegant and easy-to-understand solution?

Best regards,

Pim

Re: Concatenate all numeric columns with CATX

ballardw — Wed, 06 Jan 2021 10:57:56 GMT

A modification of first may work:

data work.option1;
   x=3;
   y=345.678;
   z=55555;
   array ___c _numeric_;	
   concat_num = catx("/", of ___c(*));
run;

How many potential variables are you concerned with? And how many digits? I suspect you may have two issues, one from a large number of numeric variables. The second is conversion of any decimal values. If you do not specify a format for the variable you may find the desired number of digits for some values truncated by a Best format.

Re: Concatenate all numeric columns with CATX

Kurt_Bremser — Wed, 06 Jan 2021 11:37:51 GMT

The question arises: why are all those columns numeric, when they are treated as character anyway?

The most elegant solution is the DICTIONARY solution, as It adapts well to changing numbers of columns, and you can expand it to calculate the length needed for the new variable, if the maximum number of digits per column is known.

Re: Concatenate all numeric columns with CATX

andreas_lds — Wed, 06 Jan 2021 11:42:49 GMT

Please have look at the documentation of catx: "In a DATA step, if the CATX function returns a value to a variable that has not previously been assigned a length, that variable is given a length of 200 bytes." And if the concatenated values need more than 200 chars, you will get

WARNING: In a call to the CATX function, the buffer allocated for the result was not long enough to contain the
concatenation of all the arguments.