topic Re: How to read multiple line data into one observation in infile statement CSV in SAS Programming

How to read multiple line data into one observation in infile CSV

Riteshdell — Mon, 09 Oct 2017 11:14:12 GMT

Hello expert,

I have a CSV File with PIPE (|) delimiter.

If you see below screenshot, I need to handle 7th column data which is going to next 4 lines till next delimiter (|).

lets assume I have 7th variable name (value) data is starting from marked dark blue color till next 4 lines.

It is reading wrong, every next line data is appearing to next column. so data is showing wrong.

How to handle this situation, can any body help me on same.

Note - I have already set enough length of column.

"Zeer goed draaiend kantoor waar de produktie de laatste maanden
fors is gestegen.
Heeft ondertussen een zilveren statuut , Naviga- Mauretus is in volle expansie en
zal weldra tot de top drie gaan behoren.
"

thanks in advance .

How to read multiple line data into one observation in infile statement CSV

Riteshdell — Mon, 09 Oct 2017 11:15:47 GMT

Hello expert,

I have a CSV File with PIPE (|) delimiter.

If you see below screenshot, I need to handle 7th column data which is going to next 4 lines till next delimiter (|).

lets assume I have 7th variable name (value) data is starting from marked dark blue color till next 4 lines.

It is reading wrong, every next line data is appearing to next column. so data is showing wrong.

How to handle this situation, can any body help me on same.

Note - I have already set enough length of column.

thanks in advance .

Re: How to read multiple line data into one observation in infile CSV

Kurt_Bremser — Mon, 09 Oct 2017 11:42:39 GMT

Please post textual data into a {i} window, so that

- formatting is preserved

- we can copy/paste it to our SAS for testing

or attach a sample of the file to your post; with this, we can even make sure to see which characters are used for the linebreaks.

Re: How to read multiple line data into one observation in infile CSV

Kurt_Bremser — Mon, 09 Oct 2017 11:45:39 GMT

Please post textual data into a {i} window, so that

- formatting is preserved

- we can copy/paste it to our SAS for testing

or attach a sample of the file to your post; with this, we can even make sure to see which characters are used for the linebreaks.

Re: How to read multiple line data into one observation in infile statement CSV

mfab — Mon, 09 Oct 2017 12:44:49 GMT

Hello there,

you could make the approach to read in line by line and then decide, what you want to do with your line.

Like so:

/* dummy code */
Data Work.Test (drop=help); Length var1 var2 var3 help $50;
Retain var1 var2 var3 help; Input; If ( countw(_infile_,"|") = 3 ) Then Do;
Output; /* output previous line */ var1 = scan(_infile_,1,"|"); var2 = input(scan(_infile_,2,"|"),??best.);
help = var2; var3 = scan(_infile_,3,"|"); End;
Else Do;
var2 = cat(var2, _infile_);
End; Run;

I think you get the idea. Read in the line and if you have the desired number of separators, you can read in all variables with scan.

Now for the lines where you have no "|" , you should take this part and add it to the variable in the previous row.

Please note that this is just an example code above.

This leaves some questions, like what to do if another variable gets too long and cut of and you might find a number of separators ,that you do not expect, etc. etc.

This link might also help.

It all has to to with checking the line and then deciding what to do with the content 😉

Cheers,

Michael

Re: How to read multiple line data into one observation in infile CSV

Tom — Mon, 09 Oct 2017 13:16:32 GMT

It depends on whether the line breaks in the middle of the lines are stored using the same characters as the file uses to mark the end of line. If you are lucky you can use the TERMSTR= option on the FILENAME or INFILE statement and SAS will be able to tell the difference.

Otherwise search on this site for solutions to this problem.

https://communities.sas.com/t5/General-SAS-Programming/Carriage-Returns-Need-to-be-removed/td-p/83079

Re: How to read multiple line data into one observation in infile statement CSV

Ksharp — Mon, 09 Oct 2017 13:27:06 GMT

Here is an example.

data have;
infile cards truncover;
input x $800.;
length line $ 20000;
retain line;
line=cats(line,x);
if countc(line,'|')=3 then do;output;call missing(line);end;
drop x;
cards;
1|a|ddfd|dsd
2|b|sds
sds|sdsd 
3|c|s
dskdh|asdf
;
run;

Re: How to read multiple line data into one observation in infile CSV

Kurt_Bremser — Mon, 09 Oct 2017 13:42:12 GMT

I merged two threads; there seems to have been a glitch with the forum SW that created multiple identical posts (including mine and @Tom's).