BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
jlaw
Calcite | Level 5


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!

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

8 REPLIES 8
art297
Opal | Level 21

: 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
Calcite | Level 5

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

Thank you for your assistance!

Tom
Super User Tom
Super User

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
Calcite | Level 5

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
Calcite | Level 5

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
Super User Tom
Super User

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
Calcite | Level 5

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

Tom
Super User Tom
Super User

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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