Hi everybody,
I would like to read a CSV file into SAS (SAS 9.4) which has commas as decimal points. The number of decimal places are different. Here is an example of my csv file:
ID | pop |
5119 | 77,09 |
5120 | 74,52 |
5122 | 89,54 |
5124 | 168,39 |
5154 | 1233 |
5158 | 407,21 |
5162 | 576,42 |
5166 | 563,2 |
5170 | 1042,81 |
I am looking for a format like BEST12. but using commas instead of periods as decimal points. Does that exist? If not, is there another easy way to read these numbers properly without transforming the numbers to character variables and exchange "," to "."? (I have 200 of these variables, so i need something more "practical").
Thank you very much in advance!
Reading a CSV file should mean you have a data step to read the text. You can specify an informat such as NUMXw. to read the values.
If you used proc import to start the process then the log should have a data step as generated to read the data. Copy the code from the log and paste into the editor. Clean up stuff like line numbers if present and change the INFORMAT for the variables to the NUMX. If lazy use the NUMX32. which likely replaces a BEST32. in the code.
Change the informat only for the variables you need.
At the same time you should check whether variables that should not be numeric are read as character (ID is likely and this is important if you have leading 0)
This is not CSV data at all, but probably what you see in Excel when you mistakenly opened the csv file with it.
Please post the raw data. To do this, open the file with a text editor (Windows Editor or, still better, Notepad++), and copy/paste the contents into a window opened with the </> button. Only this will keep the data as-is.
Reading a CSV file should mean you have a data step to read the text. You can specify an informat such as NUMXw. to read the values.
If you used proc import to start the process then the log should have a data step as generated to read the data. Copy the code from the log and paste into the editor. Clean up stuff like line numbers if present and change the INFORMAT for the variables to the NUMX. If lazy use the NUMX32. which likely replaces a BEST32. in the code.
Change the informat only for the variables you need.
At the same time you should check whether variables that should not be numeric are read as character (ID is likely and this is important if you have leading 0)
The convention "123.456,67" is often used in the EU. If your file contains any dates in the form mm/dd/yyyy, make sure that they are not actually in the form dd/mm/yyyy, which is also common in the EU. Depending on your data, you might not immediately see an error even if the month and day are reversed.
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.