Display decimal point as a comma instead of period for numeric variables when importing csv file

[KarimaTouati]

Hello all,

I have a csv file that I am trying to import into SAS EG 8.3 using the import data icone.

My problem is with the numeric variables.

This is an example of my data in the csv file : 

You can see that I have a periode as a decimal separator ( the yellow column as an example).

My problem comes when I import the data with SAS EG 8.3. Here is how it looks like : 

 It doesn't recognize the periode as a decimal separator.

I have tried to format the varible type with 

format MNT_VOL commax10.5;

or with

Mnt_VOL=translate(Mnt_VOL,'.',',');

but I have always a problem to fix it.

Any help will be appreciated. Thank you in advance.

[JosvanderVelden]

Can you send us the code generated when you use the import data icon?

Can you use the import data icon with the attached csv file (first line is header)?

Best regards, Jos

[KarimaTouati]

This is the code genarated when importing your data sample with the import data icon  : 

DATA WORK.sample;
26             LENGTH
27                 Year               8
28                 Make             $ 5
29                 Model            $ 38
30                 Description      $ 33
31                 Price              8 ;
32             FORMAT
33                 Year             BEST4.
34                 Make             $CHAR5.
35                 Model            $CHAR38.
36                 Description      $CHAR33.
37                 Price            BEST7. ;
38             INFORMAT
39                 Year             BEST4.
40                 Make             $CHAR5.
41                 Model            $CHAR38.
42                 Description      $CHAR33.
43                 Price            BEST7. ;
44             INFILE '/saswork/SAS_work4E9600003067_SRV-SAS/#LN00010'
45                 LRECL=66
46                 ENCODING="LATIN9"
47                 TERMSTR=CRLF
48                 DLM='7F'x
49                 MISSOVER
50                 DSD ;
51             INPUT
52                 Year             : ?? BEST4.
53                 Make             : $CHAR5.
54                 Model            : $CHAR38.
55                 Description      : $CHAR33.
56                 Price            : ?? COMMA7. ;
57         RUN;

[PaigeMiller]

This is not the code you used. All the variable names are different. We need working SAS data step code for your data and your variables. See: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...

[KarimaTouati]

I know how to use a proc import.

I can not use it in my case since I am working on a server and there is some admin restrictions to acess the data file location.

My recent code was in response to @JosvanderVelden.

[PaigeMiller]

---

@KarimaTouati wrote:

I can not use it in my case since I am working on a server and there is some admin restrictions to acess the data file location.

My recent code was in response to @JosvanderVelden.

---

And so the code you showed us is absolutely useless.

We need your data provided as WORKING data step code, even if you have to type it in yourself. The data can be fake, as long as the variable names and formats and such are correct. And if you help us, then we can provide better help to you. But you have to do the work to help us.

[KarimaTouati]

This is the code used for my data as well the origiinal data file :

DATA WORK.TAB2_2022;
23             LENGTH
24                 NUM_POLICE       $ 15
25                 DC_TIERCE        $ 6
26                 TYPE_SINISTRE    $ 3
27                 DATE_OUVERTURE     8
28                 DATE_MISSION_EXPERT   8
29                 TYPE_DOSSIER_AFIN $ 15
30                 IDCDL_AFIN       $ 25
31                 STATUT_AFIN        8
32                 RESERVE_AFIN       8
33                 MNT_CAS            8
34                 MNT_TIERCE         8
35                 MNT_VOL            8
36                 MNT_INC            8
37                 ALL_NBR_CREE       8
38                 ALL_MNT_CREE       8
39                 DD_QUIT_CREE     $ 1
40                 MNT_ENCAISSE       8
41                 DD_QUIT_ENCAISSE   8
42                 MNT_ARRIERE        8
43                 DD_QUIT_ARRIERE    8
44                 MNT_ANNULLEE_PF    8
45                 DD_QUIT_ANNULLEE_PF   8
46                 HONORAIRE_EXPERTS   8
47                 HONORAIRE_EXPERTS_AR   8
48                 HONORAIRE_EXPERTS_EC   8
49                 HONORAIRE_EXPERTS_SR   8
50                 DERNIERMVT       $ 23
51                 MONTANT_RESERVE_AUXILIARE   8
52                 MONTANT_RESERVE_RAJ   8
53                 STATUT_POLICE    $ 7
54                 MNT_RES_VOL        8
55                 RESERVE_AFIN_COMPTA   8 ;
56             FORMAT
57                 NUM_POLICE       $CHAR15.
58                 DC_TIERCE        $CHAR6.
2                                                          Le Système SAS                             13:50 Friday, January 13, 2023

59                 TYPE_SINISTRE    $CHAR3.
60                 DATE_OUVERTURE   DDMMYY10.
61                 DATE_MISSION_EXPERT YYMMDD10.
62                 TYPE_DOSSIER_AFIN $CHAR15.
63                 IDCDL_AFIN       $CHAR25.
64                 STATUT_AFIN      BEST1.
65                 RESERVE_AFIN     BEST5.
66                 MNT_CAS          BEST1.
67                 MNT_TIERCE       BEST1.
68                 MNT_VOL          BEST9.
69                 MNT_INC          BEST1.
70                 ALL_NBR_CREE     BEST1.
71                 ALL_MNT_CREE     BEST9.
72                 DD_QUIT_CREE     $CHAR1.
73                 MNT_ENCAISSE     BEST9.
74                 DD_QUIT_ENCAISSE DDMMYY10.
75                 MNT_ARRIERE      BEST4.
76                 DD_QUIT_ARRIERE  DDMMYY10.
77                 MNT_ANNULLEE_PF  BEST4.
78                 DD_QUIT_ANNULLEE_PF DDMMYY10.
79                 HONORAIRE_EXPERTS BEST7.
80                 HONORAIRE_EXPERTS_AR BEST6.
81                 HONORAIRE_EXPERTS_EC BEST7.
82                 HONORAIRE_EXPERTS_SR BEST7.
83                 DERNIERMVT       $CHAR23.
84                 MONTANT_RESERVE_AUXILIARE BEST7.
85                 MONTANT_RESERVE_RAJ BEST7.
86                 STATUT_POLICE    $CHAR7.
87                 MNT_RES_VOL      BEST5.
88                 RESERVE_AFIN_COMPTA BEST8. ;
89             INFORMAT
90                 NUM_POLICE       $CHAR15.
91                 DC_TIERCE        $CHAR6.
92                 TYPE_SINISTRE    $CHAR3.
93                 DATE_OUVERTURE   DDMMYY10.
94                 DATE_MISSION_EXPERT YYMMDD10.
95                 TYPE_DOSSIER_AFIN $CHAR15.
96                 IDCDL_AFIN       $CHAR25.
97                 STATUT_AFIN      BEST1.
98                 RESERVE_AFIN     BEST5.
99                 MNT_CAS          BEST1.
100                MNT_TIERCE       BEST1.
101                MNT_VOL          BEST9.
102                MNT_INC          BEST1.
103                ALL_NBR_CREE     BEST1.
104                ALL_MNT_CREE     BEST9.
105                DD_QUIT_CREE     $CHAR1.
106                MNT_ENCAISSE     BEST9.
107                DD_QUIT_ENCAISSE DDMMYY10.
108                MNT_ARRIERE      BEST4.
109                DD_QUIT_ARRIERE  DDMMYY10.
110                MNT_ANNULLEE_PF  BEST4.
111                DD_QUIT_ANNULLEE_PF DDMMYY10.
112                HONORAIRE_EXPERTS BEST7.
113                HONORAIRE_EXPERTS_AR BEST6.
114                HONORAIRE_EXPERTS_EC BEST7.
115                HONORAIRE_EXPERTS_SR BEST7.
116                DERNIERMVT       $CHAR23.
3                                                          Le Système SAS                             13:50 Friday, January 13, 2023

117                MONTANT_RESERVE_AUXILIARE BEST7.
118                MONTANT_RESERVE_RAJ BEST7.
119                STATUT_POLICE    $CHAR7.
120                MNT_RES_VOL      BEST5.
121                RESERVE_AFIN_COMPTA BEST8. ;
122            INFILE '/saswork/SAS_workDD09000073E7_SRV-SAS/#LN00010'
123                LRECL=227
124                ENCODING="LATIN9"
125                TERMSTR=CRLF
126                DLM='7F'x
127                MISSOVER
128                DSD ;
129            INPUT
130                NUM_POLICE       : $CHAR15.
131                DC_TIERCE        : $CHAR6.
132                TYPE_SINISTRE    : $CHAR3.
133                DATE_OUVERTURE   : ?? DDMMYY10.
134                DATE_MISSION_EXPERT : ?? YYMMDD8.
135                TYPE_DOSSIER_AFIN : $CHAR15.
136                IDCDL_AFIN       : $CHAR25.
137                STATUT_AFIN      : ?? BEST1.
138                RESERVE_AFIN     : ?? COMMAX5.
139                MNT_CAS          : ?? BEST1.
140                MNT_TIERCE       : ?? BEST1.
141                MNT_VOL          : ?? COMMAX9.
142                MNT_INC          : ?? BEST1.
143                ALL_NBR_CREE     : ?? BEST1.
144                ALL_MNT_CREE     : ?? COMMAX9.
145                DD_QUIT_CREE     : $CHAR1.
146                MNT_ENCAISSE     : ?? COMMAX9.
147                DD_QUIT_ENCAISSE : ?? DDMMYY10.
148                MNT_ARRIERE      : ?? BEST4.
149                DD_QUIT_ARRIERE  : ?? DDMMYY10.
150                MNT_ANNULLEE_PF  : ?? BEST4.
151                DD_QUIT_ANNULLEE_PF : ?? DDMMYY10.
152                HONORAIRE_EXPERTS : ?? COMMA7.
153                HONORAIRE_EXPERTS_AR : ?? COMMAX6.
154                HONORAIRE_EXPERTS_EC : ?? COMMA7.
155                HONORAIRE_EXPERTS_SR : ?? COMMA7.
156                DERNIERMVT       : $CHAR23.
157                MONTANT_RESERVE_AUXILIARE : ?? COMMAX7.
158                MONTANT_RESERVE_RAJ : ?? COMMAX7.
159                STATUT_POLICE    : $CHAR7.
160                MNT_RES_VOL      : ?? COMMAX5.
161                RESERVE_AFIN_COMPTA : ?? COMMAX8. ;
162        RUN;

[PaigeMiller]

I never download attachments. 

So your post needs to contain DATA, the actual numbers and character strings in your data (or even fake numbers and fake character strings, which would be fine), as working SAS data step code. I have provided a link to instructions. Please follow those instructions.

[AMSAS]

@KarimaTouati 
We are really going to see your data and code, a lot of people (including me) will not download attachments for obvious reasons.

One thing I do not understand is how your raw data is CSV format. If it is then how would you know if the following is 3 values (123, 12 and example, ie 2 numbers and a text string) or just 2 (123.12 and example ie 1 number and a text string)?

123,12,example

Even if you have column headers (see below) you would still have issues

Var1, Var2
123,45,67

Would this be 123 and 45.67, or 123.45 and 67

It would be helpful if you could create some SAS code that replicates your raw data file e.g.

data _null_ ;
	file "myTemp.csv" ;
	do i=1 to 10 ;
		x=ranuni(1)*10 ;
		y=ranuni(2)*10 ;
		put x commax5.2 "," y commax5.2 ;
	end ;
run ;
		

[Tom]

I would recommend that you not IMPORT the file.  Instead just READ the file.  That is write your own data step to read the file. Then you have complete control over the names of the variables and the INFORMAT that is used to read each variable.

If the file is only available on the PC that is running Enterprise Guide then use the File Upload task to copy the CSV file up to the SAS server.

If you really have no idea what is in the file then you could see if PROC IMPORT changes how it decides to read strings like that if you change the LOCALE setting to a location where the default is to use comma for decimal points.

Or use some other tool to GUESS how to read the file.  Such as this macro: https://github.com/sasutils/macros/blob/master/csv2ds.sas

that has a method for you to override the informat used for a variable.