About Sven111

Sven111 · ‎03-23-2017

Yes that should work, but if you have the plain values for both columns in both tables, why not just match up PLAIN_A to PLAIN_B directly? You wouldn't need to worry about hashing anything, you could just use a standard PROC SQL JOIN. You could do a join on the hashed values too once you created HASH_B, but again that seems like it's just an extra step that isn't really providing any value. What problem are you actually trying to solve? Hashing is a great tool to have handy, but I'm not sure it's doing you any favors in this case, unless you're just trying to learn how to use it for its own sake.

Sven111 · ‎03-23-2017

I'm not sure I'm 100% following your question, let me see if I got this. You have a table (call it TABLE_A) that contains (at least) 2 columns, one of the columns being some text or numeric values (we'll call it column PLAIN_A) and another column which contains the hashed value of contents of column A (call it column HASH_A). Is your question can you lookup values in that table using either PLAIN_A or HASH_A as a key? If so, then yes if you know one of the values in the columns. You need to be careful with hash functions though since they're very sensitive to how data is fed to them, make sure everything is (or isn't) quoted identically. Any change in the input to the hash function will (by design) completely change the hash value.

Sven111 · ‎03-22-2017

We found an entry for ORACLE_HOME in the backup we made prior to upgrade in the sasenv_local file. We readded it to the file and it looks like it's working again. Thanks!

Sven111 · ‎03-22-2017

Oracle appears to be working ok, I think @maheshtalla is on the right track with the environmental vars. I've gone through the SAS ACCESS install/config guide but didn't see anything about modifying the environmental vars, I also went through all of the sasenv files and didn't see anything looking like it's referencing Oracle. And sasenv_local appears to be stock, everything is commented out instructions.

Sven111 · ‎03-22-2017

We just finished upgrading from 9.4M2 to 9.4M4 (Linux environment), but now our connection to Oracle isn't working right. When we try to setup an Oracle LIBNAME we get the following error: UNABLE TO CONNECT TO ORACLE-ORACLE ENVIRONMENT IS EITHER NOT SET OR SET INCORRECTLY! I've done some searching for this error message, but have come up dry. SAS appears to be working in general if we're not connecting to Oracle. It seems like it might be saying it's missing some environmental variables somewhere, but my user environment didn't change at all. Does SAS have it's own environmental variables that may have been overwritten in the upgrade maybe? Any ideas?

Sven111 · ‎03-20-2017

Interesting article. I do a lot of back and forth between Oracle and SAS, it took me a lot of time to learn most of the stuff you wrote up since it's not in one convenient place. I did notice you used the SAS option SQL_IP_TRACE, which I can't find very much documentation on from SAS's help or elsewhere. Can you point me to someplace that explains this option and the values it can take?

Sven111 · ‎03-20-2017

You need to be careful with DATE/TIMESTAMP's when moving between Oracle and SAS. I don't recall exactly which off the top of my head, but I know that when moving between the two SAS interprets Oracle DATES or TIMESTAMPS as either SAS DATETIMES or CHAR's, I think there is also a difference between TIMESTAMP and TIMESTAMP WITH TIMEZONE and TIMESTAMP WITH LOCAL TIMEZONE, in whether SAS will treat it as a DATETIME or CHAR. Also, Oracle does not have a concept of a pure date, even the DATE type includes a time component. My guess is that this type mismatch is causing your problems.

Sven111 · ‎03-14-2017

That's unfortunate, Oracle is usually quite helpful with error codes on stuff like this, but I've only used Teradata very briefly so don't know what else I can offer. The only other thing I'd suggest is to continue to pare down the code until you get a CREATE PROCEDURE that actually works, even if it's not really doing anything besides creating an empty procedure or something. Once you get something that actually runs, you can start adding in bits and pieces until the errors come back, which should tell you what's going wrong. Otherwise I guess you can open a SAS Support ticket, or maybe someone else will have better insight into Teradata specific troubleshooting. Sorry I wasn't of more help.

Sven111 · ‎03-14-2017

What do you have set for the SASTRACE options? I usually run my code with the following so any Oracle error messages are passed back to the SAS Log, that may help determine why Teradata doesn't like the code if you haven't already. OPTIONS NOSTSUFFIX; OPTIONS SASTRACELOC=SASLOG; OPTIONS SASTRACE=',,t,dbs';

Sven111 · ‎03-14-2017

Interesting. I wouldn't think creating/replacing a procedure should be a problem. I've run all sorts of stuff in Oracle using explicit passthrough, and I don't think Teradata would be different. My guess is that it is a quoting problem, I've run into similar problems and they're always a pain to track down. Have you tried creating a much simpler procedure, ideally one without all the internal quoting, to see if that works? If so, then you'll know it's a quoting issue and can start adding parts back in piecemeal to try and track down what's going wrong.

Sven111 · ‎03-10-2017

I did some more exploring and haven't been able to find any other formats that seem to be passing through (of the ones I use anyways). I also briefly tried creating some custom formats to see if I could get them to pass over, but no dice. So at this point it looks like just the COMMA format works. That's better than nothing. Maybe I'll try again once my server gets updated to 9.4M4 to see if that changes anything.

Sven111 · ‎03-10-2017

@rogerjdeangelis It's kind of hard to follow this since it isn't clear what's text and what's code, and it looks like there may be portions of SAS log files mixed in. I gather however that the method you're outlining only works on a Windows system since I see references to OLEdb and possibly VBscript, but I'm running in a Linux environment and don't think those will work. I actually already am familiar with multiple methods of exporting to Excel (those available in Linux at least), but am primarily looking for info on formats passing through to Excel via PROC EXPORT since I didn't think that was possible prior to yesterday.

Sven111 · ‎03-10-2017

If your reading from an Oracle DB it may not be the designers fault. Oracle doesn't have a standard date only type. Even the type called DATE has hours, minutes and seconds included whether you want them or not. As a result when SAS pulls a DATE column from Oracle it treats it as a DATETIME, since that's really what it is.

Sven111 · ‎03-10-2017

@Reeza Yeah, that's what I always thought too. If nothing else I plan on switching all of the DOLLAR formatted variables to COMMA since that's closer to what I want. I'll probably try out some other formats or variations to see if I can get anything else to pass through though. I was hoping there was a list of working ones somewhere to save me the effort. Currently my server is on 9.4M2, but we're scheduled to upgrade to M4 next week (for the second or third time, so we'll see if it actually happens). I may try ODS EXCEL once we finally get there. I played around with the limited ODS EXCEL functionality in M2 a while back but ran into some problems so stuck with PROC EXPORT.

Sven111 · ‎03-10-2017

@art297 Unfortunately the code will be running on Linux server via a crontab, so I can't use your macro, but I took a look anyways and it's quite nice. It gave me a few ideas to improve some of the code in my macro libary.

Re: Is there any way to disconnect the pre-assigned oracle libraries w...

Re: Oracle ODBC driver

Re: Ask the experts / comment on column ...is

Re: Ask the experts / comment on column ...is

Re: Ask the experts / comment on column ...is

Re: How to encrypt password on log?

Re: How to encrypt password on log?

Re: Oracle passthrough syntax frustration

Re: How SAS output the special charater in string (from Oracle server)...

Re: Find users who are pulling in large datasets from Oracle to SAS

Re: ERROR: Ambiguous reference, column wing_ is in more than one table...

Re: Increase variable name length from 32 to 128 characters

Re: Increase variable name length from 32 to 128 characters

Re: Find users who are pulling in large datasets from Oracle to SAS

Update DBMS tables faster from SAS

Re: Appending a sheet to an already existing Excel document with ODS

Re: How to force updates to SAS Log?

Re: How to encrypt password on log?

Re: How SAS output the special charater in string (from Oracle server)...

Re: Insert SAS table values into Oracle table

Re: Comparing and reading a hash value

Re: Comparing and reading a hash value

Re: SAS-Oracle connection error after upgrading to 9.4M4 from M2

Re: SAS-Oracle connection error after upgrading to 9.4M4 from M2

SAS-Oracle connection error after upgrading to 9.4M4 from M2

Re: DBIDIRECTEXEC: GOFAST=YES for Database Processing

Re: DATETIME20 Format error on where clause

Re: Teradata CREATE or REPLACE PROCEUDRE with explicit SQL passed to D...

Re: Teradata CREATE or REPLACE PROCEUDRE with explicit SQL passed to D...

Re: Teradata CREATE or REPLACE PROCEUDRE with explicit SQL passed to D...

Re: Preserving Formatting in Excel via PROC EXPORT

Re: Preserving Formatting in Excel via PROC EXPORT

Re: Problem using MONTH() function

Re: Preserving Formatting in Excel via PROC EXPORT

Re: Preserving Formatting in Excel via PROC EXPORT