sas >> Remove Carriage Return Symbol ?

by jamesgreen55 » Tue, 10 Feb 2004 02:53:57 GMT

Hi Folks! SAS Ver8.2 Win2000

I am importing an MS Access97 data table with the following code;

PROC IMPORT OUT= WORK.TEST
DATATABLE= "TEST _OUTPUT"
DBMS=ACCESS97 REPLACE;
DATABASE="\\james\SAS_INPUT\data_ms_access97.mdb";
RUN;

Problem is that one of the comment fields allows multi-lines. When SAS
reads the data in I get an odd symbol representing the carriage return
in the value.

It looks like two pipe symbols together '||' only bold and somewhat
smaller? I would like to use the following code to replace the symbol
with a space.

comment=translate(comment,'','|');

How do I get SAS to display that character?

Better yet how do I get SAS to replace a carriage return with a space
in place of a symbol when importing? A search of the archives didn't
help...

TIA
James

sas >> Remove Carriage Return Symbol ?

by Richard A. DeVenezia » Tue, 10 Feb 2004 04:21:55 GMT



* make a string containing all chars you dont want;
length charsToSpace $256;
do i = 1 to 32, 127 to 255;
substr(charsToSpace,1,1) = byte(i);
end;

* translate unwanted chars to spaces;
spacedOut = translate (accessString, charsToSpace, '');

you might also consider 1
1. using compress
2. maintain statistics on what was spaced out and how many times. Examine
garbage report to determine if improvements at source are sensible. Pointy
hairs are not sensible.

--
Richard A. DeVenezia
http://www.devenezia.com/downloads/sas/macros/

sas >> Remove Carriage Return Symbol ?

by Richard A. DeVenezia » Tue, 10 Feb 2004 06:24:19 GMT

Typoops -


should be
substr(charsToSpace,I,1) = byte(I);

--
Richard
(thanks Ron)

sas >> Remove Carriage Return Symbol ?

by pchoate » Tue, 10 Feb 2004 06:59:45 GMT

Hi James -

You should look at the character in hex. Once you know its value you can
use compress (or other functions) to remove it.

Something like this will put 20 byte chunks of it into your log lined up
with the 40 bytes of hex equivalent.

Data _null_;
comment='this is line of text';

length piece $20;
l=length(comment);
Put // 'Record = ' _n_;
Do I = 1 to l by 20;
Piece= substr(comment,I);
Put I= @;
Do J = 1 to 20;
sub=substr(Piece,J,1);
Put sub @;
End;
Put / I= Piece $hex40.;
End;
Put /;
run;

/**log***/
Record = 1
I=1 t h i s i s l i n e o f t
I=1 7468697320697320C6206C696E6520206F662074
I=21 e x t
I=21 6578742020202020202020202020202020202020

Note it shows you that is 'C6'x.

This following code removes all non-numeric/alpha/space characters, you
could throw in comma, quotes, etc. so it would just keep typewriter
characters:

data test;
garbage=';t!@e#$%s^&*t( )_c-+o><l> clean=
Compress(garbage, Compress (Collate(0,255),
Upcase ('abcdefghijklmnopqrstuvwxyz') ||
Lowcase('abcdefghijklmnopqrstuvwxyz') ||
(' 0123456789')));
put clean;
run;


Here's the ASCII and EBCDIC tables: http://www.asciitable.com/

The typewriter keys are 9 (tab), and 32-127 (or '09'x and '20'x-'7F'x) in
the ASCII table, the rest are non-printing. CR/LF are 13 and 10, which are
0D/0A in hex.

The syntax for taking out all CR's and LF's is

comment= Compress(comment,'0D0A'x);

If you just want CRLF pairs you something like

comment= TRANWRD(comment,'0D0A'x,'');

HTH

Paul Choate
DDS Data Extraction
(916) 654-2160

-----Original Message-----
From: James Green [mailto: XXXX@XXXXX.COM ]
Sent: Monday, February 09, 2004 10:54 AM
To: XXXX@XXXXX.COM
Subject: Remove Carriage Return Symbol ?

Hi Folks! SAS Ver8.2 Win2000

I am importing an MS Access97 data table with the following code;

PROC IMPORT OUT= WORK.TEST
DATATABLE= "TEST _OUTPUT"
DBMS=ACCESS97 REPLACE;
DATABASE="\\james\SAS_INPUT\data_ms_access97.mdb";
RUN;

Problem is that one of the comment fields allows multi-lines. When SAS
reads the data in I get an odd symbol representing the carriage return
in the value.

It looks like two pipe symbols together '||' only bold and somewhat
smaller? I would like to use the following code to replace the symbol
with a space.

comment=translate(comment,'','|');

How do I get SAS to display that character?

Better yet how do I get SAS to replace a carriage return with a space
in place of a symbol when importing? A search of the archives didn't
help...

TIA
James

Similar Threads

1. RE : Remove Carriage Return Symbol ? Answer found!

2. Parsing Raw Data to remove Carriage Returns

3. Printing Carriage Returns

4. Problem with carriage return/newline character when writing

I am pretty sure the offending character is '0A'x.  It is inserted
into the comment field in EXCEL by <ALT><ENTER> key combination, to
produce a new line in a cell.  You can easily translate it in your SAS
program to a blank using the translate function.

comment = translate(comment,' ','0a'x);


On Fri, Apr 11, 2008 at 12:41 PM,  < XXXX@XXXXX.COM > wrote:
> I had a table in Excel which included an ID (identification number),
> COMMENTS, and AMOUNT column. The COMMENTS column were text notes (up
> to 2000 characters) commenting on the corresponding ID. I imported
> this table (via proc import) into a SAS dataset and then used the file
> and put statements to write the dataset to a fixed-length text file
> (i.e., for each record, the ID variabe was outputted to positions 1-4,
> COMMENTS outputted to positions 6-2005, and the AMOUNT column
> outputted to positions 2007-2012).
>
> The text file, however, did not come out how I expected because some
> of the text in the COMMENTS field wrapped over to the next line in the
> text file (each record is supposed to appear on only one line in the
> text file, not multiple lines). When I looked into the COMMENT field
> in the SAS dataset I created, I noticed some tiny black, rectangular
> symbols that appeared sporadically
> throughout some of my records. I'm assuming those symbols represent a
> carriage return/newline character (I don't know what the exact
> terminology is for it)?? I noticed that wherever those symbols
> appeared in the SAS dataset is where the text file broke to a new line
> when the dataset was written to the text file. Is it possible to
> eliminate this carriage return (or whatever it's called) symbol is
> SAS? For my text file to be correct, I need to have each dataset
> record appear on only one line in the text file and it appears as
> though it is this carriage return which is making some of the comments
> in my dataset to break to unexpectedly break to the next line in the
> text file...
>

5. Problem with pipe delimited file with embedded carriage returns

6. Carriage Returns when exporting

I need to put carriage returns at the end of each record when I export a
file.  Currently, I'm using proc export; however, the software I am reading
the data into says that I don't have carriage returns.  How do I put
carriage returns at the end of each record when I export.


PROC EXPORT DATA= datafile
            OUTFILE= "c:\temp\datafile.csv"
            DBMS=CSV REPLACE;
RUN;


Thanks in advance,

Brad

7. SAS to Excel - carriage return in Excel

8. Carriage Return in %LABEL/Annotate Graphics text?

Is it possible to cause a carriage return when using the ANNOTATE
%LABEL macro, or the equivalent in ANNOTATE commands?

For example, thinking that '0D'x was a carriage return, I tried

%label(x,y,"Line 1"||'0D'x||"Line 2",black,0,0,3,simplex,5)

but that didn't work. Is this possible to do?