topic Re: PROC Import - Problem with CSV File Format in SAS Procedures

PROC Import - Problem with CSV File Format and line breaks

luj — Mon, 06 Apr 2020 12:51:03 GMT

HI! I am having a problem getting an Excel file (xlsx) in a CSV file format that I can use to import into SAS. I have a very long text column in the Excel spreadsheet. When I save the file to a CSV file and use Proc Import, it appears that the column gets read in as individual rows instead of a column. When I open the CSV file in Excel, the format is fine. If I open the CSV file in Word, I can see that portion of the text column data is displayed as a row, whether than a continuous text column. Is there some other format I should be using to prevent this happening. Unfortunately, I have several files with long text columns that are acting the same way when I want to import the file in my SAS process. I have tried saving the file as a tab delimited instead of a comma delimited file with the same results.

Thanks!

Re: PROC Import - Problem with CSV File Format

Tom — Sun, 05 Apr 2020 22:48:32 GMT

Your description is not at all clear. It would help to show a (small) example of the type of rows that are causing trouble.

Most likely the LENGTH of the strings is not the issue. Instead it is probable that the long strings contain end of line characters. Remove those and your CSV file should work.

Re: PROC Import - Problem with CSV File Format

luj — Sun, 05 Apr 2020 23:11:16 GMT

Sorry I wasn't clear. Below are screen prints of the columns as they appear in Excel, Word and SAS output and my SAS statement:

I am not sure how to identify or remove end of line characters from the Excel file.

Thanks for your help!

Re: PROC Import - Problem with CSV File Format

Tom — Sun, 05 Apr 2020 23:37:04 GMT

You could google it. https://www.google.com/search?q=remove+line+breask+in+excel

You could also just use the XSLX file directly. Then you can remove/replace the CR and/or LF characters with SAS code.

libname mylib xlsx "myfile.xlsx";
data want;
  set mylib.mysheet;
  array _c _character_;
  do over _c;
    _c=translate(compress(_c,'0D'x),' ','0A'x);
  end;
run;

Re: PROC Import - Problem with CSV File Format

luj — Mon, 06 Apr 2020 00:00:14 GMT

Thanks ... really appreciate your assistance!!!!!