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

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


 

Capture.JPG

1 ACCEPTED SOLUTION

Accepted Solutions
SVoldrich
Obsidian | Level 7
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;

View solution in original post

6 REPLIES 6
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Ammonite | Level 13 VDD
Ammonite | Level 13

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
Jade | Level 19

@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
Obsidian | Level 7
i am doing this in a passthrough query is that my problem? do functions like this need to be done explicitly?
SVoldrich
Obsidian | Level 7
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
Diamond | Level 26 RW9
Diamond | Level 26

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.  

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 6 replies
  • 1980 views
  • 0 likes
  • 4 in conversation