Hey Fleroo. Is your oracle data field a datetime field by chance? if so that's your problem (if you are doing a create table from sas to oracle it automates it as a datetime). Let me clarify that I actually am going to assume it is, because in oracle the "date" field is actually a date time field (it stores the DD-MM-yy:HH:MM:SS). However when you query the results it only returns the date portion unless you tell it specifically not too (by the way, this is one of the many reasons some people HATE oracle). Anyways, what is happening is that when sas stores a date, it actually stores a number (the number of days between 1-1-1960 and whatever date you want to display). Aka if you wanted to display 1-5-1960, it would store a 5. The issue is when you insert this into the sql server, it converts the 5 DAYS into 5 SECONDS, and your date becomes 1-1-1960:00:00:05, but again oracle only displays 1-1-1960..... So if you want to get your process to work, wrap you date into a datetime (do this by using the dmhs function, or dhms(YOURDATE,0,0,0); THen when you insert this into the database it will work! (if this was your problem of course!) Sorry you're stuck with oracle ... View more

So why don't you create the file, and then group by the two variables to get your list, and then just re-join to your data as a subset if that's all you want... Or just do a group by 2 variables, you can group by as many varaibles as you like within sas, you don't have to group by everything in a select clause (strange because you do have too in sql if it's not in an aggregation clause). I am still not understanding what you actually need. Again if you have some data, or the data step that works it would help a lot. ... View more

Sorry I'm not following, but could you give an example with before and after data that you have and need? It seems to me like you are saying you want all keys that have more than 1 record (by a set of unique keys) to be output into one dataset, and all keys that have only 1 record to be output into another dataset? Is this correct? Aka if you have loan, date, balance, term, etc... and your original key is Loan + date, then you want all loans and dates that are dupliated (01, january) into one dataset with everything else into another? Thanks for the clarification! ... View more

If you know that the variable is SUPPOSED to be numeric and people are accidently creating character values AND you ALWAYS want to remove all of the character data, just do an input to manually convert the values to numeric. Note you are changing data in doing this, and losing the record for all variables that are not numeric, but this is what you asked for (I still wouldn't recommend this without understanding why your variables aren't numeric if they're supposed to be). Anyways just do something like this proc import datafile='myfile' out=newdata replace; guessingrows=10000; run; data newdata_fixed; set newdata; NewNumericfield=input(character_Field,8.); run; This will convert all of the characters to null values within your numeric fields. ... View more

As per Tom's note 3), if you wrap code into a macro, the line length can only be a specific length (256  long I believe). If you have very long coded lines (longer than 1 - 2 screens of code) the macro is liking cutting off the code here. If you had a quoted string before it was cut off, then that will lead directly to your error. I know this because I was not aware of the macro line length issue and one of my programs had this exact problem, and it took me FOREVER to figure out what was going wrong. Ex: This will not work if wrapped into a macro data newdata; set olddata; if oldvar1='the variable to check for' and oldvar2='' and issue_comment='Remediation within process' and ........  then finalvar="The borrower needs to take immediate action to make sure a foreclosure YADA YADA YADA'; run; Basically, the sytem would cut off within your finalvar definition, so the code would only read up to the fore in foreclosure, and you would then have un-even quotes. Hope that helps! ... View more

So I am using bulkload=yes in my libname statement (as defined below) libname consent odbc dsn=my_defined_odbc_connection schema=dbo bulkload=yes DBMAX_TEXT=32000; Is there a way to determine definitively if my system is using the systems bulkload facility? Aka how would I find SAS utility library that it would load the flat file too, and then load into the system? I guess my main question is how to be sure that the bulkload is actually doing a bulkload (what if the sql server database doesn't have the bulkload facility set up, or if it has permission issues that I don't have access too, etc..). ... View more