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