topic Re: Concatenate numeric column values in SAS Programming

Concatenate numeric column values

luch25 — Mon, 31 Aug 2020 12:34:43 GMT

Hello,

I need to concatenate 100 columns of numeric values. An small example dataset is provided below. I want the concatenated variable to be separated by a space, so the variable reads like this: "41 42 43 44 45 46 47"; "61 62 63 64". Note that some rows have missing values (they don't have a value for each column).

data have;
input col1 col2 col3 col4 col5 col6 col7;
cards;
41 42 43 44 45 46 47
51 52 53 54 55 57 .
61 62 63 64 . . .
71 . . . . . . 
81 82 83 84 . . .
;

I tried the following code, as well as some other modifications, but can't quite get the correct answer. The following code does not include a space and includes "." for columns with missing values ("61626364...").

data want; set have;
all=catt(' ', of col:);
drop col:;
run;

Any advice?

Re: Concatenate numeric column values

novinosrin — Mon, 31 Aug 2020 12:41:34 GMT

HI @luch25 Would this help?

data have;
input col1 col2 col3 col4 col5 col6 col7;
cards;
41 42 43 44 45 46 47
51 52 53 54 55 57 .
61 62 63 64 . . .
71 . . . . . . 
81 82 83 84 . . .
;

data want;
 set have;
 want=translate(catx(' ',of col:),' ','.');
run;

Re: Concatenate numeric column values

luch25 — Mon, 31 Aug 2020 13:08:23 GMT

Yes, thank you!!

Re: Concatenate numeric column values

andreas_lds — Mon, 31 Aug 2020 13:14:55 GMT

Minor variation of the solution posted by @novinosrin :

options missing=' ';

data want;
   set have;
   length want $ 100;
   want = catx(' ', of col:);
run;

options missing='.';

Without the length-statement catx sets the length to 200 chars, if you list is far longer than those seven variables you should, truncation should be expected.

Re: Concatenate numeric column values

Ksharp — Mon, 31 Aug 2020 13:17:48 GMT

Alternative way is :

data have;
input col1 col2 col3 col4 col5 col6 col7;
cards;
41 42 43 44 45 46 47
51 52 53 54 55 57 .
61 62 63 64 . . .
71 . . . . . . 
81 82 83 84 . . .
;
options missing=' ';
data want;
 set have;
 want=catx(' ',of col:);
run;