Since I am not allowed to access the database, I receive unloads in the form of a CSV file. These CSV files contain 15 to 20 columns and 700.000 rows. At least 2 (sometimes even more) of these columns contain dates. In the CSV file the used format for these dates are dd/mm/yyyy. Since proc import tries to discover and to recognize data on its own, I format these dates to the SAS default format dMMMyyyy to make this process easier. I try to import these files using different methods. Proc import with a CSV file, proc import with an Excel file, data infile with a CSV file, data infile with an Excel file, or simply using File > Import Data and configure it using the SAS data import wizard. (I even use informat and format to define every single variable.) When using a CSV file, I use the ; delimiter. No matter which above method I use, the delimiter is always ignored (even though I state "delimiter = ';' ;" in my script. To give you an example, the first variable will be "employee number;firstname" and the second variable will be "lastname; division;", while it actually had to be employee number; firstname lastname; division; and so on. On top of that if I import either a CSV- or Excel file, the log file will show errors regarding invalid data. The error is "Invalid data for var in line 249 126-133". This causes more than 100.000 rows to lose all of their dates and this makes my analysis useless. I don't get why SAS thinks this is invalid data, because I even formatted it into dMMMyyyy. When I don't format the dates in the original file, SAS will automatically turn the dates into numbers. I have used several methods to change the format of these columns, but they are simply not changing to dates no matter what I do. I want to know what the best way is to handle these imports, knowing I will only receive CSV- and Excel files. I don't mind converting it to another file type or sorting the data before importing it, but I need SAS to stick to the data inside these files. My entire analysis is based on this data, so if the data goes "corrupt" at the very start, I will have a very bad time. Any ideas or suggestions?
... View more