Importing a file with multiple delimiters per record

[That____Redhead]

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!

[Jagadishkatam]

filename x "path";

Data have;

     infile x dlm='09'x dsd;

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

run;

Thanks,

Jagadish

[That____Redhead]

Hi Jagadish,

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

Thanks,

Russell    

[Tom · Accepted solution]

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;

[That____Redhead]

Wow - thanks!  Works perfect!

[data_null__]

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;

[sas_9]

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!

[Tom]

Depends on what you want that to mean.

[art297]

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

[That____Redhead]

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

[sas_9]

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

;

[art297]

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

;

[sas_9]

awesome sir, its working fine...

Thanks for clearing doubts...