Import .csv files to SAS Viya - problem with decimal separator

[MichalRa]

Hi,

I am using SAS Viya 2021.2.3. My server language option is en_US.utf8.

I've got problem with importing decimal columns correctly from local .csv files in SAS Data Explorer.
If my decimal separator is ',' (comma), columns are imported as double. If decimal separator is '.', columns are consistently imported as varchar/char.
I think that key option is Locale. I was setting up a lot of different Languace Culture Names (e.g.: en_US, en_GB, fr_FR), but this had no effect - still "6763,25" is interpreted as double, but "6763.25" is intepreted as varchar/char.

What's wrong? My dataset, my parameters in Locale option?

Thanks,
Michał

[ballardw]

Suggestion: Post a few lines of the data, or a file with similar content, in a TEXT box opened on the forum using the </> icon. Then we can see exactly what your file looks like.

Also a question that is more important than some think: have you opened that file in spreadsheet software to examine the contents or a text editor? The values displayed by spreadsheet software are notorious for not actually showing the contents for CSV as they "interpret" results and can be quite different than the actual text. To make things even worse, if you accidentally saved the file after looking at it in spreadsheet software the actual values can very well have changed from what they should be.

Just one recent example: I receive data from a source that provides dates in yyyy-mm-dd layout. When the file is opened in spreadsheet software the value displayed is in mm/dd/yyyy appearance. So if I write code to read "mm/dd/yyyy" it fails because that is not what is actually in the file.

[MichalRa]

Sample of my data - all columns are imported as (var)char:

A;126925371.56;127183662.58;132261268.70;141246436.99;144998880.42;146992358.59;260302.95;253522.42;239251.04;210525.10;198896.96;183491.82;2894.61;2928.64;2608.44;2984.45;4755.87;4781.49;41087790.33;40968743.01;41642983.10;41906439.14;42677106.60;44220988.60;740156.73;843694.35;748762.56;924246.36;843605.06;915124.92;978992.88;984860.72;972799.60;696182.25;712007.00;749274.75;978992.88;984860.72;972799.60;2243795.91;2531121.50;2396268.49;64508.45;73957.91;70400.04;52506.93;48002.26;46969.45;0.00;0.00;0.00;0.00;0.00;0.00;2101693.00;2229895.00;2238439.00;2065875.00;2273330.00;2051574.60;9781596.13;10418517.75;10486393.80;12753301.16;12698085.23;11838105.14;42912502.61;45219896.33;48129863.75;51202758.97;54584147.44;58851564.07;40750140.00;42830155.00;46335937.00;49460990.00;52188727.00;56581048.00;2162362.61;2389741.33;1793926.75;1741768.97;2395420.44;2270516.07;152143.79;3386637.60;7456591.88;7492355.95;8609667.98;9129198.78;7893566.95;7922141.98;7860509.61;7229560.61;6578597.12;6793757.17;10617863.38;6495005.28;2465468.27;5783231.52;4230144.48;2584269.25;10555774.83;6257966.35;1640545.14;5602638.86;3864607.27;2296908.64;6593452.97;6553790.61;6550159.66;6462292.09;5959339.74;5590393.59;45252968.28;44857271.79;45737398.91;45616778.04;46620312.99;48142539.24
B;47616187.49;46157444.35;53888910.78;55380016.30;58704786.63;57676305.18;282455.44;291477.37;271970.38;239278.28;208063.43;205498.58;12432.85;12872.63;12271.83;13237.27;18400.76;20078.49;17061223.28;16288936.51;17965412.12;19711170.78;18828938.77;18267449.45;462840.46;513395.54;524002.55;606921.23;606328.06;637173.60;169601.71;182659.77;143351.41;137100.44;141606.92;157998.85;169601.71;182659.77;143351.41;670555.05;630475.47;879821.23;32928.09;18243.85;13276.86;14868.37;11934.09;9605.40;0.00;0.00;0.00;0.00;0.00;0.00;15181.00;13606.00;16081.00;16119.00;6062.00;4070.00;1819145.09;2904437.83;3624684.06;1953457.13;3417032.88;2114893.73;16386070.85;16825072.45;18862146.08;20570034.34;21195145.56;22518511.24;14724629.00;15970915.00;18084324.00;19463826.00;20048530.00;21247167.00;1661441.85;854157.45;777822.08;1106208.34;1146615.56;1271344.24;60467.37;887853.07;2763858.98;3182743.36;3482242.43;3484250.16;1795938.11;1769304.54;1921465.49;2279585.89;1846709.70;1844722.88;534201.00;0.00;0.00;900000.00;3800.00;-39.09;534201.00;0.00;0.00;900000.00;0.00;0.00;1528577.44;1557792.66;1563375.86;1678339.42;1808274.13;1824347.86;18514119.99;17887337.89;19538348.83;21363526.61;20397903.96;20106998.26
C;16750795.28;18438544.15;22338352.39;23383598.59;23605601.81;26344237.87;259862.71;270047.31;263964.95;225318.96;213411.43;195617.62;19151.64;20492.33;19469.23;21715.78;39175.06;38568.99;3421400.25;3566317.06;4234001.45;4365598.36;3965408.60;4175646.19;377417.62;386608.36;389266.63;414557.82;381040.99;431505.19;32592.00;29392.66;29493.50;36510.82;43855.00;48654.02;32592.00;29392.66;29493.50;382990.47;352098.48;465635.80;5529.05;4507.58;6001.65;5773.20;6582.22;7981.06;0.00;0.00;0.00;0.00;0.00;0.00;6615.00;6270.00;5025.00;4870.00;4735.00;3605.00;1039369.60;1168032.14;1322853.83;1785259.53;2028819.74;2984410.99;9712448.18;10483397.96;12356392.57;12902928.91;12949687.05;14134304.08;9403760.00;10205933.00;11946375.00;12540942.00;12915076.00;14087472.00;308688.18;277464.96;410017.57;361986.91;34611.05;46832.08;60606.86;667409.72;1346208.09;1394319.89;1606755.07;1757002.87;607223.78;624663.96;609888.72;565462.44;411081.57;450463.09;3800.00;16680.00;0.00;189274.78;33076.80;16400.00;3800.00;0.00;0.00;189274.78;0.00;0.00;252022.56;303974.69;264355.11;329514.87;347116.57;335563.77;4562594.48;4798710.75;5402109.91;5517491.59;5041626.98;5457864.29
D;13630489.38;12748568.66;13902695.91;15288653.21;15125937.31;17187232.46;148695.11;169986.15;138174.45;117538.91;106968.70;94620.89;15511.58;15725.43;14717.91;16014.47;29189.38;28603.95;2299255.34;2574799.15;2712224.03;2871835.74;3002498.60;3005811.09;193753.30;190764.20;205770.74;212414.29;248866.92;263722.22;19024.50;15872.00;16059.00;17306.00;17905.00;17744.00;19024.50;15872.00;16059.00;301018.00;434360.15;430536.67;6338.10;4840.20;2951.10;2221.79;4330.00;2714.87;0.00;0.00;0.00;0.00;0.00;0.00;1160.00;1150.00;1520.00;1680.00;0.00;0.00;1194136.32;691057.34;803790.61;895003.47;50952.73;469647.22;7526289.73;7921564.20;7481650.12;8685482.29;8918279.91;10026024.56;7410855.00;7897933.00;7465151.00;8644940.00;8880318.00;10018671.00;115434.73;23631.20;16499.12;40542.29;37961.91;7353.56;0.00;422435.58;1006019.81;1153914.04;1401279.96;1459584.97;4156.50;873.75;2319.00;4733.84;85024.62;130040.87;4120.00;6210.00;8710.00;4140.00;0.00;0.00;0.00;0.00;0.00;0.00;0.00;0.00;59572.30;49918.28;60140.05;47178.68;33432.73;52114.86;2902966.15;3249040.03;3302850.45;3578190.19;3863754.07;3919453.98
E;372526893.02;380613465.84;386399380.65;375104120.49;375572230.23;405298841.31;186731.38;186601.38;190829.93;136829.70;120736.16;114171.74;24843.00;26900.00;24918.00;26744.00;49869.00;49447.50;76285830.63;78479702.92;83475594.16;75169302.40;72415331.65;78714070.84;1121844.57;802568.20;875933.08;986392.34;1109422.73;1243182.16;1356974.33;1393558.87;1455089.38;1271103.80;1259309.11;1249757.52;1356974.33;1393558.87;1455089.38;9404586.03;5157220.40;6310698.27;208545.73;211760.58;201397.36;185946.73;166939.01;177696.77;0.00;67650.00;38709.00;9742.00;0.00;0.00;80490.00;68510.00;68235.00;47329.00;79132.00;111404.00;55001291.13;67920687.46;64717240.23;57974840.64;59295228.98;56297331.53;136812612.67;141333643.81;151334619.09;161630824.29;164454406.67;172676114.68;131836540.00;137458610.00;146594980.00;157703794.00;161218592.00;169304621.00;4976072.67;3875033.81;4739639.09;3927030.29;3235814.67;3371493.68;3086452.31;12100130.26;19269915.99;18701252.31;20917783.68;24321454.31;17252085.42;15760506.06;16497557.81;17799459.99;14848437.26;15145367.48;21753083.29;16781784.76;4066224.92;895319.50;1926565.38;1720203.04;20801310.35;16546215.06;3707290.49;0.00;0.00;0.00;41385203.59;41260476.59;39768035.85;42728284.06;42524040.00;41231488.15;84108054.33;84859550.12;90363384.53;86826638.61;80076199.26;87620488.22

Another dataset - area is imported as double (correctly!):

"area","id","length","roadNumber"
"111,14","698",,"000008S"
"92,49","699",,"000008S"
"1,98","1053",,"009"
"1838,04","2052",,"032"
"40,20","2095",,"032"
"102,89","4115",,"380014S"
"55,96","4155",,"380014S"
"137,05","10859",,"3800 92S"
"178,18","11017",,"380093S"
"143,38","15760",,"380 308S"
"120,05","15830",,"380308S"
"24,75","155",,"000003S"

In this dataset column "area" is imported as "varchar"

"area","id","length","roadNumber"
"111.14","698",,"000008S"
"92.49","699",,"000008S"
"1.98","1053",,"009"
"1838.04","2052",,"032"
"40.20","2095",,"032"
"102.89","4115",,"380014S"
"55.96","4155",,"380014S"
"137.05","10859",,"380092S"
"178.18","11017",,"380093S"
"143.38","15760",,"380308S"
"120.05","15830",,"380308S"
"24.75","155",,"000003S"

Any locale changes do not affect behavior of file. Why?

[Kurt_Bremser]

Basically, your issue comes from the fact that you do not sue SAS code properly; instead you rely on the help that the pointy-clicky interface and PROC IMPORT is trying to give you. Maxim 31: Computers Are Dumb.

The best way to read a csv file is to write the data step code yourself, so you have full control over how variables have to be read.

To get help, open the csv file with a text editor (NOT WITH EXCEL!!) and copy/paste a few lines into a window opened with this button:

[MichalRa]

---

@Kurt_Bremser wrote:

Maxim 31: Computers Are Dumb.

---

🙂
I found a solution.

I have to... change browser language.