topic Re: How do I read every value within delimiter to a new row in SAS? in SAS Programming

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

AshleyBright — Mon, 07 Jan 2019 12:11:10 GMT

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.

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

PeterClemmensen — Mon, 07 Jan 2019 12:23:28 GMT

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;

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

AshleyBright — Mon, 07 Jan 2019 12:30:21 GMT

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.

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

kiranv_ — Mon, 07 Jan 2019 12:40:16 GMT

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
;

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

AshleyBright — Mon, 07 Jan 2019 13:30:55 GMT

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

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

kiranv_ — Mon, 07 Jan 2019 13:41:05 GMT

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

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

Cynthia_sas — Mon, 07 Jan 2019 13:46:23 GMT

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

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

AshleyBright — Mon, 07 Jan 2019 13:58:55 GMT

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

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

AshleyBright — Mon, 07 Jan 2019 14:04:32 GMT

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

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

andreas_lds — Mon, 07 Jan 2019 14:41:49 GMT

@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/99725#M27996

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

art297 — Mon, 07 Jan 2019 16:03:43 GMT

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