BookmarkSubscribeRSS Feed
angeliquec
Quartz | Level 8

 

We a have a pipe-delimited text file where some fields are longer than 32767 characters. Our client wants to place the excess characters into another column. Example, the original column is called "COL1". If one row is found to have COL1 exceeding 32767 characters, the excess ones will be placed in "COL2" and so on. Can this be possible?

 

3 REPLIES 3
Patrick
Opal | Level 21

It is possible to do but it's very painful to code and it will have a heavy impact on extract performance.

 

You would need to read your file as data stream using "recfm=N" and then read character by character until you either reach 32KB, your pipe delimiter or an end-of-line indicator (like '0A'x for a line feed); and then populate your target variables accordingly. So quite a bit of processing and cumbersome coding.

 

I've had similar requirements in past projects. Whenever I started asking "why" and "how do you intend to use this data" and also explained what it means in regards of storage required and implementation effort, such requirements got dropped.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

I would totally agree with you here Patrick.  The problem lies at data source, if the OP starts "fixing" it here it becomes another source then and opens another set of issues.  For instance, even at a basic level the data which is generated needs to be throughly checked against the source data.  The question should be asked why the data is exceeding that length, as that is a huge chunk.  Someone has obviously made a boobo somewhere along the line, or not coded/split something up correctly.

Patrick
Opal | Level 21

I would assume it's an extract from a database CLOB field (like free text applications or claims or attachments).

 

There can be reasons for a business user to want such data, eg. for searches or text analytics, but: A CLOB field can also get exported into separate files stored in a content server only storing the links to the content server in SAS files.

 

In real life I've been only in one project so far where we really needed to provide access to such files (for SAS Intelligence Management).

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 3 replies
  • 1984 views
  • 1 like
  • 3 in conversation