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!
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.
: 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;
I will give this a try and let you know how it works.
Thank you for your assistance!
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
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!
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
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.
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
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: