topic Import Data - Delimiter in SAS Enterprise Guide

Import Data - Delimiter

RandyW — Thu, 30 Jan 2020 18:11:38 GMT

Newer EG user here and I am trying to import a .DAT file where the Field delimiter is ~##~ and Record delimiter is ~@^*^@~

In EG, the Delimited Fields "Other" option appears to only allow one character.

Does anyone know if this delimiter type is possible?

Thanks in advance

Re: Import Data - Delimiter

Tom — Thu, 30 Jan 2020 18:32:43 GMT

Are you really trying to read a WORD file? If not can you post some of the data as text instead of as WORD attachment. If you just pasted a photograph of your data into the WORD file then post the photograph directly into your posting instead.

You can use a simple data step to see what is in the file. Something like:

data _null_;
  infile 'myfile.dat' obs=3;
  input;
  list;
run;

Or perhaps you want to treat it as a binary file since you apparently have "records" instead of "lines". In that case you might just read the first few hundred bytes of the file. Say 300 bytes in blocks of 100 each.

data _null_;
  infile 'myfile.dat' recfm=f lrecl=100 obs=3;
  input;
  list;
run;

Re: Import Data - Delimiter

TomKari — Thu, 30 Jan 2020 18:24:50 GMT

Now THAT's funky data!

I took at quick look at EG, and I don't see a way.

Are you experienced enough with the SAS tools to write some SAS code and run it in a program node in EG? Doing something like this is trivial with the underlying SAS programming language.

Another option I can think of is to use something like Notepad to convert all of your multi-character delimiters to a single character. ~ and | are popular, just make sure first that whatever it is doesn't appear in the dataset already.

Tom

Re: Import Data - Delimiter

RandyW — Thu, 30 Jan 2020 18:55:50 GMT

great idea, thank you

Re: Import Data - Delimiter

ballardw — Fri, 31 Jan 2020 17:16:35 GMT

Here's a suggestion that may get you something that works for that file:

Post some example text in a code box opened on the forum with the {I}. The picture you posted does not include an actual example of your end of record appearance. The example doesn't need to contain any sensitive values and would be a good idea to contain at least 3 records so we can see two or more "end of record"

Code would be needed but he field delimiter can be addressed with the DLMSTR option on an infile statement:

data example;
   infile datalines dlmstr="~##~";
   input a $ b c;
datalines;
abc~##~1.23~##~999
;