topic Re: Number format change when export to csv in SAS Programming

Number format change when export to csv

hhchenfx — Thu, 01 Nov 2018 03:12:03 GMT

Hi Everyone,

I export data to csv file. Then I load this csv file to a different software (MT4) and this software doesn't read number as numeric.

I have to open the csv file, select all data and click on the number setting with Icon Comma (Comma Style) in Excel.

Then save it.

Now the software read everything correctly.

In SAS code, I try to change format of all numeric into 6.2 before export. But still not helpful.

Not sure you can suggest me any procedure to fix that bug.

Thank you.

HHCFX

		proc export data=Low_dot_normal
		outfile="E:\export_value.csv" replace;run;

Re: Number format change when export to csv

Patrick — Thu, 01 Nov 2018 03:32:20 GMT

It's certainly not a bug. SAS will either use the format permanently assigned to the variable or if there isn't one then it will use the default format. So if you need numeric values written out in a specific way then make sure that you first assign the correct format to the variable.

And to check what SAS writes to a .csv don't use Excel to open the data but a text editor.

Re: Number format change when export to csv

34reqrwe — Thu, 01 Nov 2018 03:31:53 GMT

I suggest that you view the .csv file with a text editor (e.g. wordpad) before and after you make the change in excel .

What is the difference that you notice ?

Re: Number format change when export to csv

RW9 — Thu, 01 Nov 2018 09:02:58 GMT

Odds are that your proc export is writing the csv text file out using that number as character. Without seeing the raw data is hard to say. Using Excel to "fix" things is really not a good idea, CSV is a plain text file format nothing to do with Excel.

You need to explain more fully what you are doing, show example raw data:

https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712

I.e. we need to see formats and exact data.

Then show the CSV file opened using Notepad - not Excel.

Re: Number format change when export to csv

Reeza — Thu, 01 Nov 2018 14:55:14 GMT

Two possibilities.
1. SAS is exporting the value as a character. Check the csv file in a TEXT EDITOR, not Excel. If you see quoatation marks around the value, that's your issue.

2. Your other software package (MT4) is not reading the data in correctly for some reason. This may have to do with the software. A csv has no forced types so the application is usually guessing and sometimes they guess wrong.

Re: Number format change when export to csv

ballardw — Thu, 01 Nov 2018 15:05:35 GMT

@hhchenfx wrote:

Hi Everyone,

I export data to csv file. Then I load this csv file to a different software (MT4) and this software doesn't read number as numeric.

I have to open the csv file, select all data and click on the number setting with Icon Comma (Comma Style) in Excel.

Then save it.

Now the software read everything correctly.

In SAS code, I try to change format of all numeric into 6.2 before export. But still not helpful.

Not sure you can suggest me any procedure to fix that bug.

Thank you.

HHCFX
		proc export data=Low_dot_normal
		outfile="E:\export_value.csv" replace;run;

I suggest that you 1) reexport the data and 2) copy some lines from the CSV file using a plain text editor like note pat. Then paste those lines into a code box opened using the forum's {i} icon.

Then indicate which value(s) are "incorrect" by highlighting a few.

BUT since you say you setting the Excel comma style as what you want I would suggest making sure the FORMAT associated the variable(s) in question have the appropriate COMMA format assigned in SAS prior to export.