02-16-2018 05:54 PM
I've a excel file which I need to read in SAS dataset. I read convert the file to .csv then read it using proc import (as we dont have xlsx engine). My problem is the results field is not getting read correctly in SAS dataset. The ≤0.9 is getting read as 0.9. I'm not sure how to get SAS read it correctly.
I tried to save file in utf-8 csv format and read using 'utf-8' encoding but I'm getting warning "WARNING: A character that could not be transcoded was encountered".
FILENAME IN "C:\Users\ABC\Desktop\try.csv" encoding='utf-8' ;
PROC IMPORT OUT= indata
Is there a easy way to solve this issue?
02-16-2018 06:13 PM
If you've got SAS EG then a quick way to get what you want: Use the EG import wizard directly on the Excel sheet, then open up the generated import node to get to the code. Using the Excel you've posted that's what I've got:
DATA WORK.try_sas; LENGTH Test $ 3 Result1 $ 4 Result2 8 ; FORMAT Test $CHAR3. Result1 $CHAR4. Result2 F12.2 ; INFORMAT Test $CHAR3. Result1 $CHAR4. Result2 BEST12. ; INFILE 'C:\Users\ssapam\AppData\Local\Temp\SEG5412\try_sas-dc12d81483b4421d9fe9859923db56c0.txt' LRECL=10 ENCODING="WLATIN1" TERMSTR=CRLF DLM='7F'x MISSOVER DSD ; INPUT Test : $CHAR3. Result1 : $CHAR4. Result2 : BEST32. ; RUN;
Now simply modify the code to your needs
1. point the INFILE path to your .csv file
2. change the delimiter in DLM= to a comma.
3. amend column attributes (length, format, informat) as required (they don't need change if it's a once-off)
....and if this is a once off then simply use the import wizard and also select to embed the data into the project.
02-16-2018 07:30 PM
If you want to read in UTF-8 data then make sure you are running SAS with Unicode support. There are too many potential characters in UTF-8 for SAS to be able to map everyone into the 256 character limit of using single byte encodings.