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

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

12 REPLIES 12
Jagadishkatam
Amethyst | Level 16

filename x "path";

Data have;

     infile x dlm='09'x dsd;

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

run;

Thanks,

Jagadish

Thanks,
Jag
That____Redhead
Fluorite | Level 6

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

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
Fluorite | Level 6

Wow - thanks!  Works perfect!

data_null__
Jade | Level 19
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
Obsidian | Level 7

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

Depends on what you want that to mean.

art297
Opal | Level 21

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

That____Redhead
Fluorite | Level 6

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
Obsidian | Level 7

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
Opal | Level 21

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
Obsidian | Level 7

awesome sir, its working fine...

Thanks for clearing doubts...

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 connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 12 replies
  • 12803 views
  • 5 likes
  • 6 in conversation