cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
GerbenRotman
‎08-18-2022
  • 9 Posts
  • 1 Likes Given
  • 0 Solutions
  • 0 Likes Received

Re: Proc import, issue reading in incomplete dates and complete dates

by Calcite | Level 5 in SAS Programming
‎08-18-2022 05:05 AM
‎08-18-2022 05:05 AM
oh, yes, where I wrote "22/14/2011" that should have been "2/14/2011'... Thanks, I have reported it as a bug. ... View more

Re: Proc import, issue reading in incomplete dates and complete dates

by Calcite | Level 5 in SAS Programming
‎08-17-2022 01:23 PM
‎08-17-2022 01:23 PM
Ok yes, that would work but would require us to update the way we output to Excel (to use proc export). Currently we output the datasets to Excel using "libname xlsx", like so: libname outrep xlsx "<path>/outputfile.xlsx"; and then the datasets made by proc import are set to a sheet in this library. Anyways, I understand we could do it differently, avoiding the use of proc import. The main issue I was hoping that could be solved is that proc import sometimes makes such obvious wrong guesses. But I guess the solution is, just do not use proc import for that. I guess I placed too much trust in proc import. ... View more

Re: Proc import, issue reading in incomplete dates and complete dates

by Calcite | Level 5 in SAS Programming
‎08-17-2022 01:12 PM
‎08-17-2022 01:12 PM
We are using guessingrows=1000, these csv files are very unlikely to have more than 1000 rows. But, yes, we could put guessingrows=max to be even more save. It does not protect against the issue at hand though (where all rows, all two of them, are used to make the guess, but the guess is still wrong). ... View more

Re: Proc import, issue reading in incomplete dates and complete dates

by Calcite | Level 5 in SAS Programming
‎08-17-2022 01:06 PM
‎08-17-2022 01:06 PM
Yes, thanks. We may go for option 1, indeed. In general proc import worked quite well for this, there just seem to be some peculiar cases, like this one, where the guessing goes wrong. Seemingly here perhaps even due to there only being two rows. with just these two rows: 1989 22/14/2011 proc report's guess is to use: informat mmddyy10. if I just add a third row: 1989 22/14/2011 1989 it understands that it should use: informat $9. ... View more

Re: Proc import, issue reading in incomplete dates and complete dates

by Calcite | Level 5 in SAS Programming
‎08-17-2022 12:53 PM
‎08-17-2022 12:53 PM
Yes, thanks for the suggestions. Something like that I think is what we may use to replace the various proc imports that are used currently, in order to avoid the occasional guessing mistakes. It will need to be a bit more fancy that this though, probably will need some macro code, because we do need to use the column headers from the first row as variable names. I understand it is not so difficult to make, it would just have been nicer to use a built in SAS procedure rather than custom code. ... View more

Re: Proc import, issue reading in incomplete dates and complete dates

by Calcite | Level 5 in SAS Programming
‎08-17-2022 12:26 PM
‎08-17-2022 12:26 PM
The guessingrows=3 was just for the example CSV file, the one that has only 2 data rows, so was perfectly adequate there I think (there is no "programmer error" there). The program using proc import checks a folder for csv files that may have been output there by various programs of various other programmers. All it tries to do is read in all the CSV files it finds there, and then output them again to an excel file, each csv file as a separate sheet. So, all that proc import was supposed to do there is create datasets from CSV files. It seems that indeed in order to avoid these somewhat "silly" issues we may need to create some separate macro in which we read in the CSV files ourselves. That appears then to be the only way to circumvent proc imports guessing behavior. ... View more

Re: Proc import, issue reading in incomplete dates and complete dates

by Calcite | Level 5 in SAS Programming
‎08-17-2022 12:11 PM
‎08-17-2022 12:11 PM
This is part of code that reads in many CSV files daily (reads them in and combines them in an excel file). That is why it was practical to use proc import, as long as it does not run into errors like this one. I understand that "1986" is not a complete date, I would have expected that proc import would as well; that it would not "guess" to read the column containing this value using informat "mmddyy10.", and that it would instead choose to use a character format for this column like "$9."   Ideally, in our case, it would be practical to tell proc import to just treat everything as character strings, but I do not think there is an option like that. (I used guessingrows=2 and guessingrows=3 here just for illustrative purposes to show this specific issue, in the actual program it was set to 1000. Indeed in the initial post where I typed "format" I should have typed "informat", I have edited that post to correct this.) ... View more

Re: Proc import, issue reading in incomplete dates and complete dates

by Calcite | Level 5 in SAS Programming
‎08-17-2022 11:44 AM
‎08-17-2022 11:44 AM
The problem is that this is used in code scheduled to run automatically on a daily basis that reads in various CSV files created daily. The contents of the CSV files varies, we cannot use the static code as created by proc import in the log. The code really relies on proc import being able to adapt the way reads the files in based on their contents. The fact that it somehow gets in trouble with such a simple CSV file (i.e. for value "1989" followed by "2/14/2011" it guesses that it can use informat "mmddyy10.") seems to indicate to me that there is likely some simple oversight in how the guessing procedure is programmed, which is why I would classify this as a bug. This does not seem to be a problematic data case for which it would be hard to guess a proper format. ... View more

Proc import, issue reading in incomplete dates and complete dates

by Calcite | Level 5 in SAS Programming
‎08-17-2022 11:09 AM
‎08-17-2022 11:09 AM
We encounter the following problem when trying to read in a simple CSV file which on the first row has an incomplete date (only the year) and on the second row a complete date. Here is a simplified version of the CSV file that can be used to demonstrate the issue: text,start,end abc,1968,1989 def,1981,2/14/2011 When we try to read this in with proc import using guessingrows=3, or higher, like this: proc import out=test datafile='<path>/test.csv' dbms=csv replace; guessingrows=3; getnames=yes; run; then we get an error for the first data row because proc import chooses to use: "informat end mmddyy10.;", and "1989" does not follow that format. If we use guessingrows=2; then it correctly notices that the first data row is not a date and it uses "informat end best12.;" (which then of course gives, as expected, an error for the second data row). What should have happened I would think, is that with "guessingrows" set to 3 or higher it should have seen that it needs to use neither a numeric nor a date format but a character one like "informat end $9.;". It appears that this is a bug in proc import. ... View more
Contact Me
Online Status
Offline
Date Last Visited
‎08-18-2022 08:26 AM
Likes given to
View All
Quick links