BookmarkSubscribeRSS Feed
nbonda
Obsidian | Level 7

Hello SAS users,

I have a csv file on Linux box. I am trying to read this file using SAS EG. But some of the value are wrapped text (I found it when I moved this file to windows and opened in Excel, The data values I have issues are wrapped text). I am not able to read this csv file into sas dataset without out errors.

sample of csv file as follows:

 

101,"Laptop","This device is in good condition, store it  in the shelf"

102,"Monitors","we have 100 monitors, about 80 are in good condition.

and 20 need to be fixed"

103,"Mice","we have 250 mice most of them in good conditon."

 

I am using following code to read above csv file:

 data inven,

infile "infile_path" delimiter=',' DSD lrecl=32000 termstr=LF ;

attrib item_no length=8 format=11.

input item_id @;

attrib Item_name length=$8. format=$8.

input Item_name $ @;

attrib comment length=$80. format=$80.

input comment $ @;

run;

 

in second row comments variable value is wrapped text ,it has multiple end of line.

how to read this kind of CSV files? Thanks in advance.

 

 

 

4 REPLIES 4
ballardw
Super User

Issue is that you have either LF or CR/LF in the actual data. How big is the file and will you need to process this type of data again?

 

If you're going to be doing this a lot it may well be worthwhile going further back in the data stream and removing the LF or CR/LF from the data before extracting to the csv.

I recommend beating the data entry people with wet spaghetti noodles as well.

nbonda
Obsidian | Level 7

Thank you for you suggesion. It's very big file,  has few millions of rows, we get these files everyday. we are bugging data management to change it :), they are asking us to read some for now. 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

I agree with ballardw.  Go back to the source and get them to fix it.  I had it with a certain database sending CSV data, they changed a flag their end and fixed the problem.

Tom
Super User Tom
Super User

Search this site as we have answered this question many times.

1) If the file was generated on a PC and you are lucky then the actual end of records indicators will be CR+LF and the embedded line breaks will just be CR or LF.  If that is true then you can try reading the file using the INFILE option TERMSTR=CRLF.  If you move the file from a PC to Unix to process with SAS then make sure to move it as BINARY and not TEXT so that the end of line characters are not modified.

2) If the file is consistent in the use of double quotes to protect values with embedded line breaks then you can create a fixed version by reading the file character by character and replacing the embedded LF or CR with some other character.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 3721 views
  • 2 likes
  • 4 in conversation