BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
draroda
Fluorite | Level 6

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,

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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.

View solution in original post

4 REPLIES 4
Reeza
Super User

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

AFAIK the solution should be CATX. 

 

Astounding
PROC Star

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;

ballardw
Super User

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.

Reeza
Super User

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=.;

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 15085 views
  • 2 likes
  • 4 in conversation