Issues about separation of lengthy character variable values(in multiple sentences) in sas 9.4

[inquistive]

Hi experts;

I have imported data from an excel (xlsx) file into a SAS dataset. One of the variables has multiple values (ranging up to 400 characters) under it and the value shows up like this:

VariableName                            VariableValues

Comment                                 Not preferable to

                                                eatDo not buy for

                                               consumption

I tried to separate and reassign the value as follows:

data want;

set have;

If comment = "Not preferable to eatDo not buy for consumption" then comment= "Not preferable to eat. Do not buy for consumption." ;

run;

It still returned "Not preferable to eatDo not buy for consumption" under the comment .

When I copy the values from the SAS dataset and paste it in SAS work space, it prints the values in two lines (with a blank line in between) as follows:

 Not preferable to eat

(Here is a blank line)

Do not buy for consumption

I even tried following data cleaning techniques in the previous step:

comment = left(upcase(compress(comment, 'A0'x))); 

comment = left(upcase(compbl(comment))); 

It still didn't help. Any suggestions what I did wrong?

Thanks for support.

[SASJedi]

Hex code for LF is '0A'x not 'A0'x, so that might be part of the problem. However, if '0A'x is putting a hex on your data (sorry  - couldn't resist)  maybe just replace the '0A'x values with '. '? That code would look something like this:

/* Example data */
data have;
   Comment=cats("Not preferable to eat",'0A'x,"Do not buy for consumption");output;
   Comment=cats("Preferred edible",'0A'x,"Fit for human consumption");output;
run;

title "&syslast";
proc print; run;

/* Replace the LF character with '. ' */
data want;
   set have;
   Comment=translate(comment,'. ','0A'x);
run;

title "&syslast";
proc print; run;

[Tom]

Figure out what is actually in the variable since it is NOT the string you tried in the IF statement.

Most likely there are some invisible characters in there.  Things to check for are things like 

'09'x - Tab

'0A'x - Line Feed

'0D'x - Carrriage Return

'00'x - Null

'FF'x - ??

'A0'x - Non breaking space.

Do the strings have any non standard characters, like accented characters or strange graphical symbols? 

Perhaps you just want to change all of the weird characters into spaces?

comment = compbl(translate(comment,' ',compress(comment,collate(32,126))));

Use TRANSLATE to convert any character in the string that is not in the ASCII codes between a space and a tilde.

Use COMPBL to convert multiple adjacent spaces into one space.