BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
marieK
Obsidian | Level 7

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!

 

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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)

View solution in original post

4 REPLIES 4
Kurt_Bremser
Super User

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.

ballardw
Super User

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)

marieK
Obsidian | Level 7
Thank you very much! The NUMXw. was exactly what i needed!
JackHamilton
Lapis Lazuli | Level 10

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.

sas-innovate-wordmark-2025-midnight.png

Register Today!

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.


Register now!

How to connect to databases in SAS Viya

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.

Discussion stats
  • 4 replies
  • 5838 views
  • 1 like
  • 4 in conversation