Excel and SAS cannot accurately store a number beyond 15 digits, so if you want your data in Excel as a number, it will be somewhat incorrect, and exponential is the most appropriate way to show it. To demonstrate, if in Excel you type 123456789123456789, Excel will display it as 1.23457E+17. If you then explicitly display it with a "Number" format, it will come out as 123456789123456000, so you can see you've lost the digits after the 15th. On the other hand, if you want it in Excel as character, which is the only way to present a representation of an 18 digit number, I think that your code is functioning correctly. Note that as soon as you try to execute numeric operations on it in Excel, however, it will revert to 15 digits of accuracy. Tom ... View more

Can you get decent results by using the SOUNDEX function on each of your strings, and then doing a direct match? That would greatly reduce the resources required. Tom ... View more

I'm pretty sure that with this syntax every record from small_table is being matched against every record in big_table (this is called a cartesian join), in other words you're executing 30 billion comparisons, which is absolutely brutal. This is definitely a case of needing a different approach...I haven't done this before, but I'm sure that someone will have an excellent suggestion soon. Tom ... View more

Looks like you didn't get any bites here, either. Send me a direct message, let me know your email address, and we'll discuss some other options offline. Tom ... View more

1. No, there are two issues. a) is SAS creating MCID as a character variable with a length of 11, or if the outcome of the PUT function is less than 11 characters on the first record, SAS might be creating it shorter. b) (Tom's question, I think) If MCID is 11 characters, how is the PUT function placing the numeric digits in the variable...with spaces at the front, or at the back. 2. Just fishing with the COMMIT question, I've bumped into it with other DBMS products, and I've never used Teradata. 3. I agree, with your LOG snippet the dataset looks good. What about running some code like the following, to see what your numbers look like? set &userid.FINALMBRmcids(obs=100); run; proc print; run That might tell you if your numbers are in the correct format. Tom ... View more

1. I don't have access to SAS right now, so I can't check it, but is it possible that if your number is less than 11 digits, SAS will create a smaller variable? You could add a LENGTH MCID $11; statement before your assignment. 2. Could be you can't see your data because you haven't issued a COMMIT to Teradata? 3. Definitely take a look at &userid.FINALMBRmcids in Teradata, and if possible check out the metadata to ensure your fields are defined correctly. If you can't access Teradata direcly, start with Select count(*) from &userid.FINALMBRmcids; in PROC SQL to see how many records are there, and then pull a few or all of them into a SAS dataset to see what they look like. Tom ... View more

TNS is an Oracle communication facility. This may be an Oracle connection problem, with nothing to do with SAS. What I usually suggest to people in this situation is to connect to the Oracle database with an Oracle tool from your SAS server (I'm not familiar with Oracle APEX, but in my environment I would suggest to connect directly to Oracle with SQL*Plus, and make sure you can access your data.) Don't just get a connection, make sure you can actually see data, as this will establish that there aren't any permission problems. If you can do this, and it's very important that you do it from the machine that you are using as your SAS server, the problem is probably with your SAS connection. Otherwise, you need to get the Oracle part of it working first, then move on to the SAS. Let us know what happens, and we can continue from there. Tom ... View more

What a muddle! I think you're all correct, but in different ways. I do agree that b and c are equivalent, but only because of the WHERE clause. Because the question lists b as the right answer, and the justification is the need to distinguish address because it is in both tables, I'm speculating that the sample SQL statement was pulled in from a different question, and the person drafting the question didn't pay attention to the WHERE clause. Because the question specifically requests ADDRESS from SIZE, I think that's what they wanted. Clearly, either the question or the proposed answer needs to be reworked. Tom ... View more

1. You are correct about the FORMAT approach. Because it's in-memory, it will be very fast, but uses up a lot of memory and you may run out. So, it's the fastest but the MOST memory intensive, sigh. 2. I have limited experience with hash table joins, so won't comment. 3. If your data is in SAS datasets, I believe you'll see similar performance from a sort and merge and from a SQL join, as behind the covers SQL will need to sort both datasets, and that's the expensive part. 4. If your data is in a database, depending on circumstances you might get the best results from pushing a JOIN to the database engine. It's worth trying, see if it's better, worse, or your DBA comes after you with a gun. 5. If you can sort and keep both datasets in the sequence of your join key, that will very fast with either a join or a sort and merge (sort is usually optimized to be very fast if the data is almost in the correct sequence). Tom ... View more

Encoding is a big subject! Here is a link to a SAS paper that discusses internationalization, which is where encoding comes in. The paper should give you the information you need to solve your problem. Good luck! Tom http://support.sas.com/resources/papers/proceedings12/254-2012.pdf ... View more