topic How to import a txt file with multiple delimiters? in New SAS User

How to import a txt file with multiple delimiters?

ashhazard — Tue, 15 Oct 2019 15:02:39 GMT

I'm trying to import a text file that has been extracted from a MUMPS database. However, it is both comma delimited and caret delimited. See example below. Can anyone tell me how to import this type of file?

^MUC("TXHAMMS",40622,733614)
300^300^300^300^300^300^300^300^300^300
^MUC("TXHAMMS",40622,733853)
300^300^300^300^300^300^300^300^300^300
^MUC("TXHAMMS",40622,734148)
300^300^300^300^300^300^300^300^300^300
^MUC("TXHAMMS2",36708,711017)
200^200^200^200^200^200^200^200^200^200^200^200^200^200^200^200^200^200^200^200^200^200^200^200^200^200
^MUC("TXHAMMS2",36708,728648)
200^200^200^200^200^200^200^200^200^200^200^200^200^200^200^200^200^200^200^200^200^200^200^200^200^200
^MUC("TXHAMMS2",36708,731084)
200^200^200^200^200^200^200^200^200^200^200^200^200^200^200^200^200^200^200^200^200^200^200^200^200^200
^MUC("TXHAMMS2",36708,731869)
200^200^^^^^^^200^^^^^^^200^200^200^200^200^200^200^200^200^200^200
^MUC("TXHAMMS2",36708,732348)
200^200^200^200^200^200^200^200^200^200^200^200^200^200^200^200^200^200^200^200^200^200^200^200^200^200
^MUC("TXHAMMS2",36708,732351)
200^200^^^^^^^200^^^^^^^200^200^200^200^200^200^200^200^200^^
^MUC("TXHAMMS2A",34903,733584)
200^^200^200^^200^200^^200^200^^200
^MUC("TXHAMMS2A",34903,733586)
200^^200^200^^200^200^^200^200^^200
^MUC("TXHBZLC",40884,719357)
100^100^100^100^100^100^100^100^100^100^^100^100^100^100^100^100^100^100^100^^^^100^100^100^100^100^^100
^MUC("TXHBZLC",40884,719358)
100^100^100^100^100^100^100^100^100^100^^100^100^100^100^100^100^100^100^100^^^^100^100^100^100^100^^100
^MUC("TXHBZLC",40884,719425)
100^100^100^100^100^100^100^100^100^100^^100^100^100^100^100^100^100^100^100^^^^100^100^100^100^100^^100
^MUC("TXHBZLC",40884,719426)
100^100^100^100^100^100^100^100^100^100^^100^100^100^100^100^100^100^100^100^^^^100^100^100^100^100^^100

Re: How to import a txt file with multiple delimiters?

Patrick — Tue, 15 Oct 2019 23:37:39 GMT

You can define more than one character as delimiter. As long as a delimiting character can't also be a valid character in another string, things will be rather simple.

data demo;
  infile datalines truncover dlm='^,()' dsd;
  input (var1-var5) (:$20.);
  datalines;
^MUC("TXHAMMS2",36708,728648)
;
proc print;
run;

Re: How to import a txt file with multiple delimiters?

ashhazard — Fri, 18 Oct 2019 15:19:32 GMT

Thank you!

This does work except for one issue - all the variable for one observation are not on the same row. For example, the below is one observation and all the variables should be on the same row.

^MUC("TXHAMMS",40622,733614)
300^300^300^300^300^300^300^300^300^300

In the data I'm importing all of the rows that end with a ")" are where the next row of variables are apart of the same observation. Is it possible to incorporate that?

Re: How to import a txt file with multiple delimiters?

Patrick — Sat, 19 Oct 2019 00:03:11 GMT

If the sample you've posted is representative of your real data then the input statement could be as simple as below.

data demo;
  infile datalines truncover dlm='^,()' dsd;
      input 
        #1 @2 (line1Var1-line1Var4) (:$20.) 
        #2 @1 (line2Var1-line2Var50) (:best32.)
        ; 
  datalines;
^MUC("TXHAMMS",40622,733614)
300^300^300^300^300^300^300^300^300^300
^MUC("TXHAMMS",40622,733853)
300^300^300^300^300^300^300^300^300^300
^MUC("TXHAMMS",40622,734148)
300^300^300^300^300^300^300^300^300^300
^MUC("TXHAMMS2",36708,711017)
200^200^200^200^200^200^200^200^200^200^200^200^200^200^200^200^200^200^200^200^200^200^200^200^200^200
^MUC("TXHAMMS2",36708,728648)
200^200^200^200^200^200^200^200^200^200^200^200^200^200^200^200^200^200^200^200^200^200^200^200^200^200
;
proc print;
run;

It looks like the even rows can have a varying number of data elements. If that's the case then consider to read the data into a long structure (=creating an output observation per "cell" in source). It requires that this line of data is all of the same type as it would end up in a single output variable. Is this something you'd like to do?