SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Importing a file with multiple delimiters per record

Accepted Solution Solved
Reply
Contributor
Posts: 25
Accepted Solution

Importing a file with multiple delimiters per record

I really think this other team just doesn't like me... they sent me a .log file today where the first 4 variables are tab delimited, then the next 10 variables are comma delimited.  And I can't use an @PUT statement, because while the tab delimited variables are all 36 characters, the comma delimited variables are different lengths.

Anyone have a suggestion?  I think I stumped google...

Thanks!


Accepted Solutions
Solution
‎10-22-2013 12:09 PM
Super User
Super User
Posts: 6,502

Re: Importing a file with multiple delimiters per record

If it is just the case that all rows have the same number of values and it is using two different delimiters then you can tell SAS to use both tab and comma as a delimiter.

infile 'myfile' dsd dlm='092C'x .... ;

or

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

infile 'myfile' dsd dlm=dlm ... ;

But if instead they have tried to store a hierarchical structure into a flat file where the a particular tab delimited field can have a different number of commas within it per observation then you will need to take a two step approach of reading the comma delimited values as character strings and then parsing out the individual values.

data want;

   length fixed1 fixed2 fixed3 fixed4 8 text5-text10 $36 ;

   infile myfile dsd dlm='09'x truncover ;

   input fixed1-fixed4 text5-text10 ;

   array var5 var51 - var59 ;

   do i=1 by 9 ;

      var5(i) = scan(text5,i,',');

    end;

....

run;

View solution in original post


All Replies
Trusted Advisor
Posts: 1,131

Re: Importing a file with multiple delimiters per record

filename x "path";

Data have;

     infile x dlm='09'x dsd;

     input var1 var2 var3 .......;

run;

Thanks,

Jagadish

Thanks,
Jag
Contributor
Posts: 25

Re: Importing a file with multiple delimiters per record

Hi Jagadish,

That only is going to import the tab delimited.  I need the comma delimited to be individual variables as well.

Thanks,

Russell    

Solution
‎10-22-2013 12:09 PM
Super User
Super User
Posts: 6,502

Re: Importing a file with multiple delimiters per record

If it is just the case that all rows have the same number of values and it is using two different delimiters then you can tell SAS to use both tab and comma as a delimiter.

infile 'myfile' dsd dlm='092C'x .... ;

or

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

infile 'myfile' dsd dlm=dlm ... ;

But if instead they have tried to store a hierarchical structure into a flat file where the a particular tab delimited field can have a different number of commas within it per observation then you will need to take a two step approach of reading the comma delimited values as character strings and then parsing out the individual values.

data want;

   length fixed1 fixed2 fixed3 fixed4 8 text5-text10 $36 ;

   infile myfile dsd dlm='09'x truncover ;

   input fixed1-fixed4 text5-text10 ;

   array var5 var51 - var59 ;

   do i=1 by 9 ;

      var5(i) = scan(text5,i,',');

    end;

....

run;

Contributor
Posts: 25

Re: Importing a file with multiple delimiters per record

Wow - thanks!  Works perfect!

Respected Advisor
Posts: 3,777

Re: Importing a file with multiple delimiters per record

data dlm;
   length dlm $1;
  
infile cards dlm=dlm dsd;
  
do dlm='|';
     
input a b c @;
      end;
  
do dlm = ',';
     
input d e f;
      end;
  
cards;
1|2|3|99,98,19
221|212||9559,98,1009
;;;;
   run;
Regular Contributor
Posts: 220

Re: Importing a file with multiple delimiters per record

guys then how can we resolve this scenario...?

cards,

12,13|14,15|16,17

5|6|,7,8|9,10

90,20,|15,.,.|12

;

Thanks!

Super User
Super User
Posts: 6,502

Re: Importing a file with multiple delimiters per record

Depends on what you want that to mean.

PROC Star
Posts: 7,364

Re: Importing a file with multiple delimiters per record

: Couldn't you just use  dlm='|,'   ?

Contributor
Posts: 25

Re: Importing a file with multiple delimiters per record

Hi Sandy,

You can either use what Sandip said or if you want to use hexadecimal it would be '7C2C'x

Full list of hexadecimal codes can be found here: ASCII Codes in Hex, Octal, and Decimal

Regular Contributor
Posts: 220

Re: Importing a file with multiple delimiters per record

what i meant was, what if we have data file contains rendom delimiter for one observation ('|', ',')  but not in same order for each observation and with same number of variables for all observation with some missing values for some observation too ? -  how we read that kind of data?

i have shown example what i have in my mind...data like below...

cards,

12,13|14,15|16,17

5|6|,7,8|9,10

90,20,|15,.,.|12

;

PROC Star
Posts: 7,364

Re: Importing a file with multiple delimiters per record

Not sure what you mean.  The following would work if your data represent 7 variables and the first record is simply missing the 7th variable:

data have;

  infile cards dlm='|,' dsd truncover;

  input x1-x7;

cards;

12,13|14,15|16,17

5|6|,7,8|9,10

90,20,|15,.,.|12

;

Regular Contributor
Posts: 220

Re: Importing a file with multiple delimiters per record

awesome sir, its working fine...

Thanks for clearing doubts...

☑ This topic is solved.

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

Discussion stats
  • 12 replies
  • 4129 views
  • 4 likes
  • 6 in conversation