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!
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;
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!
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;
Thanks ... really appreciate your assistance!!!!!
Catch the best of SAS Innovate 2025 — anytime, anywhere. Stream powerful keynotes, real-world demos, and game-changing insights from the world’s leading data and AI minds.
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.
Ready to level-up your skills? Choose your own adventure.