get rid of CRLF in data

[SVoldrich]

i've been reading through the other posts/questions about CRLF and i can't seem to get it to go without error.

Situation: i have one data element, one Entry that has a CRLF at the end of the data. 

Creating a pipe delimited txt file and that CRLF is making things not work.

When i do this:

SELECT DISTINCT
TRANWARD(B.MED_SCHL_COND_NM,'0D0A'x,'') AS MED_SCHOOL_NM,

B.MED_SCHL_LOC AS MED_SCHOOL_LOC

FROM GGDD.MED_SCHL B

i get this error:
ERROR: CLI describe error: [IBM][CLI Driver][DB2] SQL0104N An unexpected token "X" was found following "". Expected tokens may
include: "AT MICROSECONDS MICROSECOND SECONDS SECOND MINUTES MINUTE HOURS". SQLSTATE=42601

if i try this:

SELECT DISTINCT
prxchange('s/(\n|\r)//', -1, B.MED_SCHL_COND_NM) AS MED_SCHOOL_NM,

B.MED_SCHL_LOC AS MED_SCHOOL_LOC
FROM GGDD.MED_SCHL B

I get this error:
ERROR: CLI describe error: [IBM][CLI Driver][DB2] SQL0440N No authorized routine named "PRXCHANGE" of type "" having compatible
arguments was found. SQLSTATE=42884

[RW9]

First option, remove the option to enter special characters on your database, it is never a good idea to allow this or capture those things.

Next up, there is no such thing as TRANWARD in SAS.  Is that a database function, if so you need to apply database syntax - nothing to do with SAS.

What I would do is this:
1) Extract the data from the database, no processing, just create a dataset as it comes out, select *.

2) With the data you now have in a dataset, use a dataset and apply compress function to keep only numbers and characters - and any others you might want to keep:

data want;
  set have;
  med_schl_cond_nm=compress(med_schl_cond_nm," ","kda");
run;

3) Export that modified dataset.

[VDD]

where is the second double quote in you code for this statement

TRANWARD(B.MED_SCHL_COND_NM,'0D0A'x,'') AS MED_SCHOOL_NM,

[andreas_lds]

---

@VDD wrote:

where is the second double quote in you code for this statement

TRANWARD(B.MED_SCHL_COND_NM,'0D0A'x,'') AS MED_SCHOOL_NM,

---

There is no single double quote, but two single quotes.

[SVoldrich]

i am doing this in a passthrough query is that my problem? do functions like this need to be done explicitly?

[SVoldrich · Accepted solution]

i was able to get it to work by leaving my passthrough alone... then creating another table that grabs those data elements proc sql; create table work.SCHOOL AS SELECT Compress(MED_SCHOOL_NM,'0D0A'x) AS MED_SCHOOL_NM, MED_SCHOOL_LOC FROM WORK.SCHOOL1 ;QUIT;

[RW9]

So, effectively what I posted above then...

Anyways, if the code is passed through, that means all the code gets picked up and passed to the database, therefore it has to conform to syntax understood by the database, using functions that the database understands.  the '..'x is a SAS specific syntax, so the database probably wont understand it.  If you want to see in action, write the code directly in the database, you will see the problems.

Oh, and please avoid coding all in uppercase, and use the code window, its the {i} above post area, so we can clearly read your code.