topic Re: Importing a file with multiple delimiters per record in SAS Data Management

Importing a file with multiple delimiters per record

That____Redhead — Tue, 22 Oct 2013 15:15:55 GMT

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!

Re: Importing a file with multiple delimiters per record

Jagadishkatam — Tue, 22 Oct 2013 15:22:28 GMT

filename x "path";

Data have;

infile x dlm='09'x dsd;

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

run;

Thanks,

Jagadish

Re: Importing a file with multiple delimiters per record

That____Redhead — Tue, 22 Oct 2013 15:28:03 GMT

Hi Jagadish,

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

Thanks,

Russell

Re: Importing a file with multiple delimiters per record

Tom — Tue, 22 Oct 2013 16:09:54 GMT

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

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;

Re: Importing a file with multiple delimiters per record

data_null__ — Tue, 22 Oct 2013 16:19:38 GMT

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;

Re: Importing a file with multiple delimiters per record

That____Redhead — Tue, 22 Oct 2013 16:57:05 GMT

Wow - thanks! Works perfect!

Re: Importing a file with multiple delimiters per record

sas_9 — Tue, 22 Oct 2013 17:16:19 GMT

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!

Re: Importing a file with multiple delimiters per record

Tom — Tue, 22 Oct 2013 17:23:23 GMT

Depends on what you want that to mean.

Re: Importing a file with multiple delimiters per record

art297 — Tue, 22 Oct 2013 17:24:55 GMT

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

Re: Importing a file with multiple delimiters per record

That____Redhead — Tue, 22 Oct 2013 17:28:00 GMT

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

Re: Importing a file with multiple delimiters per record

sas_9 — Tue, 22 Oct 2013 19:00:28 GMT

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

;

Re: Importing a file with multiple delimiters per record

art297 — Tue, 22 Oct 2013 19:11:22 GMT

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

;

Re: Importing a file with multiple delimiters per record

sas_9 — Tue, 22 Oct 2013 19:44:24 GMT

awesome sir, its working fine...

Thanks for clearing doubts...