BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
luj
Calcite | Level 5 luj
Calcite | Level 5

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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;

View solution in original post

4 REPLIES 4
Tom
Super User Tom
Super User
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.
luj
Calcite | Level 5 luj
Calcite | Level 5

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:

luj_0-1586127938919.pngluj_1-1586128020125.png

 

luj_2-1586128118222.pngluj_3-1586128182653.png

 

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

Thanks for your help!

 

 

 

Tom
Super User Tom
Super User

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;
luj
Calcite | Level 5 luj
Calcite | Level 5

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

SAS INNOVATE 2024

Innovate_SAS_Blue.png

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Get the $99 certification deal.jpg

 

 

Back in the Classroom!

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

View all other training opportunities.

Discussion stats
  • 4 replies
  • 546 views
  • 1 like
  • 2 in conversation