Import file with two delimiters

[jlaw]

I have a file that has two delimiters, tab & comma that I need to import into SAS 9.1.

Any assistance would be appreciated.

thank you!

[art297]

: In an infile statement you can include a delimiter option and that can include multiple delimiters, but I can't figure out how to do it with a tab and a comma.  On the other hand, you could just do something like:

data want;

  infile "c:\art\test\test.txt" delimiter=',' truncover;

  input @;

  informat x $3.;

  _infile_=translate(_infile_,',','09'x);

  input x y z;

run;

[jlaw]

I will give this a try and let you know how it works.

Thank you for your assistance!

[Tom]

If you mean that you want to treat the presence of either a TAB or a COMMA is indicating the start of a new field then you can set the DLM= option on the INFILE statement to a variable that you have populated with that value.

dlm='09'x || ',' ;

infile 'myfile' dlm=dlm ;

But is that what you want or is what you really have is a nested structure.  Frequently you might see a file that is tab delimited and one of the fields is a text sting that is really a list of values separated by commas.   Here is an example where I have replace  TAB with | so that it is readable online.

ID|GENDER|MULTISELECT|DATE

1|M|1,3|01JAN2010

2|F|2,3,4,5|01FEB2011

To read this you need to define MUTLISELECT as a character variable.  Then I could loop over it and create multiple variables (or multilple rows) that show the set of values nested inside of it.  So to turn it into rows you could do something like:

data want ;

  length id 8 gender $1 date choice select 8 multiselect $20 ;

  informat date date9.;

  format date date9.;

  infile cards dsd dlm='|' truncover firstobs=2;

  input id gender multiselect date ;

  do choice=1 by 1 until (missing(select)) ;

    select = input(scan(multiselect,choice,','),best.) ;

    if not missing(select) or choice=1 then output;

  end;

  drop multiselect ;

cards;

ID|GENDER|MULTISELECT|DATE

1|M|1,3|01JAN2010

2|F|2,3,4,5|01FEB2011

run;

Obs    id    gender      date       choice    select

1      1      M       01JAN2010       1         1

2      1      M       01JAN2010       2         3

3      2      F       01FEB2011       1         2

4      2      F       01FEB2011       2         3

5      2      F       01FEB2011       3         4

6      2      F       01FEB2011       4         5

[jlaw]

Tom - this is useful to know, I may need this in the future...but I am definitely looking for how to import with two existing delimiters.

Thank you for your reply!

[jlaw]

I attempted Arthur's method and almost got it to work but wasn't quite working the way it should. The columns were importing with inappropriate widths, although I did adjust in regards to my data.

I also attempted Tom's first method and got stuck.

While although its not the best solution I was able to come up with at least a temporary work around so that I can still automate the process.

Answer:

#1) Import file with tab delimiter

#2) Remove commas from all the columns

#3) Export with comma delimiter

#4) Import file with formats

Thank you for your responses & I am still open to more effecient way to accomplish the task of importing a double delimited file, contains tab delimiter & comma delimiter in file to import.

For example:

company,<tab>companynumber,<tab>DATE

ABC,<tab> 1234,<tab>01/01/1987

[Tom · Accepted solution]

Did you try the DLMSTR option on the INFILE statement.  If you set DLMSTR='2C09'X then it will treat <comma><tab> as the delimiter between fields.

13   data test ;

14    infile userfile dlmstr='2C09'x truncover ;

15    input (x1-x5) (:$20.);

16    put (x1-x5) (=);

17   run;

x1=company x2=companynumber x3=DATE x4=  x5=

x1=ABC x2=1234 x3=01/01/1987 x4=  x5=

NOTE: 2 records were read from the infile USERFILE.

[jlaw]

Tom - this did work, thank you!  It inserted extra blank columns between each column, but very easy to work with.

Thank you again for your assistnce

[Tom]

Sounds like you used DLM= instead of DLMSTR= and that you also specified DSD.

You could try NOT specifying DSD in the INFILE.  That would eliminate the empty columns.  But it would also shift values to the left if you do have actual empty cells in the input.