DATA Step, Macro, Functions and more

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

Reply
Contributor
Posts: 73

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

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

 

 

 

DATA.PNG

 

thanks in advance .

Super User
Posts: 10,588

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

Posted in reply to Ritesh_dellvostro

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.

 

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super User
Posts: 10,588

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

Posted in reply to Ritesh_dellvostro

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.

 

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super User
Super User
Posts: 8,281

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

Posted in reply to Ritesh_dellvostro

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/8307...

 

Contributor
Posts: 73

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

Posted in reply to Ritesh_dellvostro

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

 

 

 

DATA.PNG

 

thanks in advance .

Frequent Contributor
Posts: 131

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

Posted in reply to Ritesh_dellvostro

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

Super User
Posts: 10,850

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

Posted in reply to Ritesh_dellvostro

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;
Super User
Posts: 10,588

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

Posted in reply to Ritesh_dellvostro

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Ask a Question
Discussion stats
  • 7 replies
  • 627 views
  • 0 likes
  • 5 in conversation