topic Re: How to read this pipe separated data. in SAS Programming

How to read this pipe separated data.

Chikku — Tue, 19 Jul 2016 12:23:29 GMT

Hi all,

Please find the below sample data which is separated by pipe symbol(|).

i am not able to read this file.please help me in importing/reading the file.

regards,

karthik

/****************************samp.txt**************************************************/

NAME||AGE||SCORE_ENG||SCORE_ECO||SCORE_STAT||ROLL_NO||CLASS||RANK
AA||||12||||||15||||||20||||||25||||00||||X||||||1
BBB||||14||||||12||||||13||||||22||||01||||X||||||1
CC||||16||||||12||||||11||||||23||||02||||IX||||||1
DD||||18||||||13||||||16||||||25||||03||||XI||||||1
EEE||||13||||||14||||||14||||||05||||04||||X||||||1
FFFFF||||17||||||16||||||16||||||14||||05||||XII||||||1

Re: How to read this pipe separated data.

RW9 — Tue, 19 Jul 2016 12:35:55 GMT

Why do you have two delimiters at each data item? That's no longer a delimeted file. Personally I would reject the file, and get the sender to provide a proper delimited file (i.e. data items separated by one character).

What you have is going to make it quite hard to read in. For instance the first line shows 8 variables, but the first data line:

AA||||12||||||15||||||20||||||25||||00||||X||||||1

Shows 7 data points, which one should be blank? Is age 12 or missing? Its a bit of a mess, and the responsibility of the file provider to sort it out.

Re: How to read this pipe separated data.

Tom — Tue, 19 Jul 2016 12:44:38 GMT

Looks like someone took a regular file and converted all of the spaces into two pipe characters.

Try using DLM='|' but without the DSD option.

data check;
  infile cards dlm='|' truncover ;
  length x1-x10 $50;
  input x1-x10;
cards;
NAME||AGE||SCORE_ENG||SCORE_ECO||SCORE_STAT||ROLL_NO||CLASS||RANK
AA||||12||||||15||||||20||||||25||||00||||X||||||1
BBB||||14||||||12||||||13||||||22||||01||||X||||||1
CC||||16||||||12||||||11||||||23||||02||||IX||||||1
DD||||18||||||13||||||16||||||25||||03||||XI||||||1
EEE||||13||||||14||||||14||||||05||||04||||X||||||1
FFFFF||||17||||||16||||||16||||||14||||05||||XII||||||1
;
proc print; run;

Re: How to read this pipe separated data.

Ksharp — Wed, 20 Jul 2016 02:12:50 GMT

Delimiter is double pipe . You need TRANWRD() function .

data check;
  infile cards dlm='|' dsd truncover;
  input @;
  _infile_=tranwrd(_infile_,'||','|'); put _infile_;
  input (x1-x20) (:$10.);
cards;
NAME||AGE||SCORE_ENG||SCORE_ECO||SCORE_STAT||ROLL_NO||CLASS||RANK
AA||||12||||||15||||||20||||||25||||00||||X||||||1
BBB||||14||||||12||||||13||||||22||||01||||X||||||1
CC||||16||||||12||||||11||||||23||||02||||IX||||||1
DD||||18||||||13||||||16||||||25||||03||||XI||||||1
EEE||||13||||||14||||||14||||||05||||04||||X||||||1
FFFFF||||17||||||16||||||16||||||14||||05||||XII||||||1
;
proc print; run;

Re: How to read this pipe separated data.

ballardw — Wed, 20 Jul 2016 14:48:05 GMT

This works for me on my machine with your text file:

filename t "d:\data\test.txt";

data junk;
   infile t dlmstr="||"  firstobs=2;
   informat name $5. age score_eng score_eco score_stat best8. roll_no class $4. rank $8.;
   input  name  age score_eng score_eco score_stat roll_no class rank ;
run;

However, you have a bunch of TABS at the end of the lines for Class IX XI and X, different numbers of tabs. Which is why I'm reading Rank as a string. It fails as a number because with the "delimiter" being "||" it attempts to read those tabs at the end as part of the value.