topic Re: concatenate non missing values in SAS Programming

concatenate non missing values

draroda — Wed, 12 Jul 2017 14:49:10 GMT

Hi All,

I have data as below.

data x;
input x1 x2 x3 x4 x5;
datalines;
1 2 3 4 5
. 2 3 4 5
1 . 3 4 5
1 2 . 4 5
. . 3 4 .
. . . . 5
;

I want to concatenate all this non missing values only.

Final output should contain all non missing values seperated by "-".

I have used below function but didnt work for me.

ch=catx("-",of x1-x5);

Can anyone please help me here ?

Regards,

Re: concatenate non missing values

Reeza — Wed, 12 Jul 2017 14:52:59 GMT

Show the code that didn't work and what you want as output.

AFAIK the solution should be CATX.

Re: concatenate non missing values

Astounding — Wed, 12 Jul 2017 15:06:49 GMT

To work with the nonmissing values only, you will need to write a little code. For example:

data want;

set have;

array x {5};

length ch $ 20;

do _n_=1 to 5;

if x{_n_} > . then ch = catx('-', ch, x{_n_});

end;

run;

Re: concatenate non missing values

ballardw — Wed, 12 Jul 2017 15:13:42 GMT

Please look at this:

options missing=' ';
data work.junk;
input x1 x2 x3 x4 x5;
ch=catx("-",of x1-x5);
datalines;
1 2 3 4 5
. 2 3 4 5
1 . 3 4 5
1 2 . 4 5
. . 3 4 .
. . . . 5
;

If you values are numeric then by default the missing value is returned for the CATX function when the numbers are turned into strings to concatenate. Changing the option missing to a blank character means allows CATX to strip the "blanks".

Don't forget to reset the Option after the data step this is needed.

"didnt work for me" is awful vague.

Are there errors in the log?: Post the code and log in a code box opened with the {i} to maintain formatting of error messages.

No output? Post any log in a code box.

Unexpected output? Provide input data in the form of a dataset, the actual results and the expected results. Data should be in the form of a data step. Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.

Re: concatenate non missing values

Reeza — Wed, 12 Jul 2017 15:14:11 GMT

It seems CATX will deal with missing characters properly, but when it converts the numeric to character it becomes a period which is no longer a 'missing' value. IMO this is not the expected behaviour though it does line up with the documentation.

The CATX function first copies item-1 to the result, omitting leading and trailing blanks. Then for each subsequent argument item-i, i=2, …, n, if item-i contains at least one non-blank character, then CATX appends delimiterand item-i to the result, omitting leading and trailing blanks from item-i. CATX does not insert the delimiter at the beginning or end of the result. Blank items do not produce delimiters at the beginning or end of the result, nor do blank items produce multiple consecutive delimiters.

If item is numeric, then its value is converted to a character string by using the BESTw. format. In this case, SAS does not write a note to the log. For more information, see The Basics.

Various solutions include:

1. Read in as character instead of numeric

2. Loop (see @Astounding solution).

3. Set option missing to a blank instead of ., see below.

options missing='';
data x;
input x1 x2 x3 x4 x5;
want = catx('-', of x1-x5);
datalines;
1 2 3 4 5
. 2 3 4 5
1 . 3 4 5
1 2 . 4 5
. . 3 4 .
. . . . 5
;
run;
options missing=.;