SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

question about csv data before importing into SAS

Accepted Solution Solved
Reply
Contributor
Posts: 38
Accepted Solution

question about csv data before importing into SAS

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.


question.png

Accepted Solutions
Solution
‎04-28-2015 09:44 AM
Super User
Super User
Posts: 6,502

Re: question about csv data before importing into SAS

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;

View solution in original post


All Replies
Solution
‎04-28-2015 09:44 AM
Super User
Super User
Posts: 6,502

Re: question about csv data before importing into SAS

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;

Super User
Super User
Posts: 7,430

Re: question about csv data before importing into SAS

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.

Contributor
Posts: 38

Re: question about csv data before importing into SAS

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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 392 views
  • 5 likes
  • 3 in conversation