How do I read every value within delimiter to a new row in SAS?

[AshleyBright]

I have a delimited file like this:

abc|def|ghi

klm|nop|qrs

and I want the output to appear like this:

abc

def

ghi

klm

nop

qrs

Please help.

Thank you.

[PeterClemmensen]

Do like this

data have;
input string $50.;
datalines;
abc|def|ghi
klm|nop|qrs
;

data want(keep=word);
   set have;
   do i=1 to countw(string, '|');
      word=scan(string, i, '|');
      output;
   end;
run;

[AshleyBright]

Thanks for your quick response Draycut! And the problem for me here is I have the data in a CSV file which is inconsistent. The file doesn't have a header. And the no. of delimited values are not fixed within each row. Moreover, length of each row exceeds the allowed limit. So I can't have this in a separate datastep. I should somehow finish this while reading it in the first step.

[kiranv_]

very similar to @PeterClemmensen solution 

data want(drop = i);
length col1 $ 3;
input @ ;
do i = 1 to countw(_infile_,'|');
col1 = scan(_infile_,i,'|');
output;
end;
datalines;
abc|def|ghi
klm|nop|qrs
;

[AshleyBright]

@PeterClemmensen and @kiranv_ thanks a lot for your quick responses. I really appreciate that. The no. of characters in each of those delimited rows is as high as 100,000. So I am not able to hold that whole row as one record and my data is truncating. Could you please guide me on that please.. For example, I have abc|def|ghi|....| upto 40,000 characters.

[kiranv_]

just wondering to know whether you really need to read that long text. dos it make sense for your business.

[AshleyBright]

@kiranv_ It's all delimited values and is very wide. And the width of the rows are not consistent throughout. Yes, it is important for me to read the data and load it..

[Cynthia_sas]

Hi:
Have you looked at LRECL= option of the INFILE statement to set your INPUT buffer to a longer length? Or the LRECL system option?
Cynthia

[AshleyBright]

@Cynthia_sas I have set the LRECL to 32760 with the infile option. I have rows longer than that 😞

[andreas_lds]

---

@AshleyBright wrote:

@Cynthia_sas I have set the LRECL to 32760 with the infile option. I have rows longer than that 😞

---

This older post seems to have an solution: https://communities.sas.com/t5/SAS-Procedures/Losing-characters-during-reading-data-from-file/m-p/99...

[art297 · Accepted solution]

Couldn't you use something like:

data want;
  infile "/folders/myfolders/long.txt" lrecl=200000 delimiter='|';
  length var $10;
  input var @@;
run;

Art, CEO, AnalystFinder.com