topic question about csv data before importing into SAS in SAS Data Management

question about csv data before importing into SAS

comeon2012 — Tue, 28 Apr 2015 13:34:05 GMT

Dear all,

To import excel data into SAS, I usually convert the excel file to csv file. But for a file as attached, when I converted the excel data to csv data, the leading zeros in some observations are missed. Also, some observations with "E" are treated as scientific format, which is not I want. Accordingly, the SAS data generated are as what the csv file shows.

I want the SAS data generated like the data in excel. Please indicate how can I do this? Thanks.

Re: question about csv data before importing into SAS

Tom — Tue, 28 Apr 2015 13:44:27 GMT

SAS will not do what you have described with a CSV file unless you tell it to. Excel will do that unless you force it NOT to.

You have pasted a picture of what looks like an Excel sheet.

Paste a copy of the actual CSV file. Open it with NOTEPAD or other text editor to see what is in it.

The advantage of using a CSV file to transfer to SAS instead of trying to convert the Excel directly is that you can write you own DATA step to read it. If you want the values to be treated as character then read them into a character variable.

data want ;

infile 'myfile.csv' dsd firstobs=2 ;

length Excel $10 CSV 8 ;

input excel csv ;

run;

Re: question about csv data before importing into SAS

RW9 — Tue, 28 Apr 2015 13:45:17 GMT

I think you are doing a last step which confuses you. When you save the Excel file to CSV, goto the saved file and right click and open with Notepad. Look at the data there. It will be corrrect. What is happening is you are then opening the CSV in Excel. Excel has a "feature" that when it reads data, it interprets what it sees and because of the E which is for exponent it converts to number. Ensure, in your SAS progam, when you read it in that you datastep input states character and it should work fine.

Another good reason not to use Excel for anything.

Re: question about csv data before importing into SAS

comeon2012 — Sun, 17 May 2015 02:41:51 GMT

Thanks Tom and RW9. I have resolved the problem according to your suggestions.