Problem with formats in "Import-Wizard"

deleted_user — Tue, 26 Feb 2008 14:22:45 GMT

Hi. There's a little problem i can't resolve.
I want import data from an excel-sheet. In one of the columns are both integers and decimals in european notation (like 35.012 or 40.256,35). I can't use a format like eurox12.2 cause i get wrong values for integers (35.012/10² in this example).
In a manually written import procedure i can use the eurox. format without a value for w. or d. and it works, but the import-wizard requires values for w. and d. (min. eurox1.0).
Is there any way to use eurox. format with import-wizard?

Re: Problem with formats in "Import-Wizard"

deleted_user — Thu, 13 Mar 2008 13:18:58 GMT

Greetings!

I have attempted to reproduce the problem you describe, but have not experienced the difficulty you seem to have had.

Let me start by describing how I created my test data in Excel so as to confirm that I have data similar to yours.

Since I am was working in an English version of Excel I first went to Tools --> Options... and on the International tab changed the Decimal separator character to a comma and the Thousands separator to a period.

Then I created a new worksheet with field headings in cells A1 to C1. These were Integer, Fixed2 and Fixed4.

Next in cell A2 I placed the value 1 and in cell A3 I placed 2.345. I also formatted these cells as Euro with 0 decimal places.
Cell B2 had the value 1,23 and B3 had 2.345,67. I formatted both of these as Euro with 2 decimal places.
Finally, cell C2 had the value 1,2345 and C3 had 2.345,6789. The format on these two cells was Euro with 4 decimal places.

I then saved the Excel workbook and started Enterprise Guide 4.1.

I created a new Enterprise Guide project and then started the Import Data task by selecting File --> Import Data... and in the Open dialog selecting my test Excel workbook and Sheet1$ (I had kept Excel's default name for the worksheet).

I didn't change anything on the Region to Import or Results panels of the task, but on the Column Options panel I made these changes:
For the field Integer I changed both the Display format and Read-in format to the currency format EURO12.0.
For the field Fixed2 I changed the Display format to EURO12.2 and the Read-in format to EURO12.0.
And finally for the field Fixed4 I changed the Display format to EURO12.4 and the Read-in format to EURO12.0.

When I ran the task a data set was created with the values E1 and E2.345 for the field Integer, E1,23 and E2.345,67 for the field Fixed2 and E1,2345 and E2.345,6789 for the field Fixed4.

A note about the read-in formats I specified: By specifying the EURO format with a value of 0 for the decimals (which is the default value anyway) we are telling SAS that we want the number that's read in to be unscaled. If we were to specify a value for the decimals on the informat, then we would be instructing SAS to scale the number read in by the EURO informat by dividing it by 10**d. So a decimal value of 3 would result in the numeric value being divided by 1000. The EURO informat doesn't need to be told how many decimal places there actually are in the incoming value because it knows how to read a European formatted number and can therefore determine the position of the decimal separator for itself.

It may well be that I've misunderstood something about the structure of the data in your Excel file, in which case please let me know and I can take another look at the problem.

Regards,
David.

topic Problem with formats in "Import-Wizard" in SAS Enterprise Guide

Problem with formats in "Import-Wizard"

Re: Problem with formats in "Import-Wizard"