BookmarkSubscribeRSS Feed
Neeta
Fluorite | Level 6

Hi all,

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
DATAFILE= IN
DBMS=csv REPLACE;
GETNAMES=YES;
DATAROW=4;
RUN;

 

Is there a easy way to solve this issue?

Please help!

3 REPLIES 3
Neeta
Fluorite | Level 6

Adding sample file I'm trying to read

Patrick
Opal | Level 21

@Neeta

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.

Tom
Super User Tom
Super User

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

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

View all other training opportunities.

Discussion stats
  • 3 replies
  • 3307 views
  • 0 likes
  • 3 in conversation