Import file with two delimiters

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 10
Accepted Solution

Import file with two delimiters


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!


Accepted Solutions
Solution
‎02-14-2013 08:26 PM
Super User
Super User
Posts: 7,060

Re: Import file with two delimiters

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.

View solution in original post


All Replies
PROC Star
Posts: 7,480

Re: Import file with two delimiters

: 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;

Occasional Contributor
Posts: 10

Re: Import file with two delimiters

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

Thank you for your assistance!

Super User
Super User
Posts: 7,060

Re: Import file with two delimiters

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



Occasional Contributor
Posts: 10

Re: Import file with two delimiters

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!

Occasional Contributor
Posts: 10

Re: Import file with two delimiters

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

Solution
‎02-14-2013 08:26 PM
Super User
Super User
Posts: 7,060

Re: Import file with two delimiters

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.

Occasional Contributor
Posts: 10

Re: Import file with two delimiters

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 Smiley Happy

Super User
Super User
Posts: 7,060

Re: Import file with two delimiters

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.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 1134 views
  • 2 likes
  • 3 in conversation