topic Re: proc sql md5 and oracle-db in SAS Programming

proc sql md5 and oracle-db

F_Röder — Tue, 15 Aug 2017 13:57:54 GMT

Hello,

i have a SAS-BASE table and want to insert the data into an oracle database. One value should be replaced with a MD5-Hash.

This works:

PROC SQL;
SELECT
md5(SUBSTR(s.path,findc(s.path, '/',2)+1,findc(SUBSTR(s.path,findc(s.path, '/',2)+2), '/',2))) as key format $hex32.
from SB0520C.WRSREPORTS s;
run;

The result looks correct - like a hash-value.

But i must concanate the result:

PROC SQL;
SELECT
'BEGINN' ||
md5(SUBSTR(s.path,findc(s.path, '/',2)+1,findc(SUBSTR(s.path,findc(s.path, '/',2)+2), '/',2))) as key format $hex32. ||
'ENDING'
from SB0520C.WRSREPORTS s;
run;

But i get the error:

md5(SUBSTR(REPORT,findc(REPORT, '/',2)+1,findc(SUBSTR(REPORT,findc(REPORT, '/',2)+2), '/',2))) as key format $hex32.
__
__
73
22

76
60 ! length=16 ||
ERROR 73-322: Erwartet wird END.

ERROR 22-322: Syntaxfehler, erwartet wird eines der folgenden: eine Zeichenkette in Hochkommata, ',', AS, FORMAT, FROM, INFORMAT,
INTO, LABEL, LEN, LENGTH, TRANSCODE.

ERROR 76-322: Syntaxfehler, Anweisung wird ignoriert.

I tried it with cat - but cat has only a buffer with 200 - and the string is longer.

How can i make this?

Thank you very much

Best regards

Frank

Re: proc sql md5 and oracle-db

LinusH — Tue, 15 Aug 2017 21:02:48 GMT

You have placed the concatenation of ENDING in the wrong place.
Then I wonder what purpose does the begin and ending string have? Especially when you assign a $hex format to the column.

Re: proc sql md5 and oracle-db

F_Röder — Wed, 16 Aug 2017 06:32:57 GMT

Hello,

that´s my target:

I have a log file with entries:

/User Folders/REAL_USERNAME/Eigener Ordner/documentation.pdf

Because of data protection i want to replace the "REAL_NAME" with a MD5-Hash.

I have a SAS-Base-Table with the Log-FIle-entries and want to insert this data into an oracle-db.

Something like that:

insert into ....
SELECT
'/' ||
SUBSTR(REPORT,2, findc(REPORT, '/',2)-2) ||
'/' ||
md5(SUBSTR(REPORT,findc(REPORT, '/',2)+1,findc(SUBSTR(REPORT,findc(REPORT, '/',2)+2), '/',2))) as key format $hex32. length=16 ||....

from SB0520C.WRSREPORTS s;

So i must concanate each part of the string --> but this don´t work.

I tried it also with cat - but this also didn´t work and beyond this cat has just a buffer with 200 bytes - and this is to short.

How can i make this? Or does someone has a better solution?

Thank you very much

Best regards

Frank

Re: proc sql md5 and oracle-db

LinusH — Wed, 16 Aug 2017 06:39:02 GMT

Since you are doing the operation and get the error in SAS I can't see that Oracle isn't part of the problem.
What happens if you correct the syntax in your first post?

Re: proc sql md5 and oracle-db

F_Röder — Wed, 16 Aug 2017 06:54:22 GMT

Wow - very fast:)

the select of post 1 works . It is just a select from a sas-base-table.

The HASH-value looks correct.

The i try the insert into an oracle-db:

PROC SQL;
INSERT INTO oraSY052.STF_LFA_I_LOGFILE_2016
(REPORT_PATH)
SELECT
md5(SUBSTR(s.path,findc(s.path, '/',2)+1,findc(SUBSTR(s.path,findc(s.path, '/',2)+2), '/',2))) as key format $hex32.
from SB0520C.WRSREPORTS s;
run;

The inserted values don´t look correct ( itried it also without "as key format $hex32."):

“ââ!³LÙÏ€PÉ¿_‰
ÿŒé8Ž€]eºEéš\ÏÒˆ

So the INSERT concerts the data and make it corrupt --> that´s the first problem.

But in my real project i have a case-query in the select-statement and must concanate the md5-value with some other strings (this is an example without concanating the string):

INSERT INTO &_INPUT.
(REPORTNAME, REPORT_PATH, SASSESSION, DESCRIPTION, CODE, SESSIONS, USERID, STAND_DATUM_ZEIT, STAND_DATUM, LAST_UPDATE)
SELECT
substr(REPORT,findc(REPORT, '/', "B") + 1),
CASE
SUBSTR(REPORT,2, findc(SUBSTR(REPORT,2), '/')-1)
WHEN
'User Folders'
THEN
md5(SUBSTR(s.path,findc(s.path, '/',2)+1,findc(SUBSTR(s.path,findc(s.path, '/',2)+2), '/',2))) as key format $hex32.
ELSE
REPORT
END AS REPORT_PATH,
SASSESSION,
DESCRIPTION,......

Here i get the errors:

Zeile 4.308: ERROR 73-322: Erwartet wird END.

Zeile 4.310: ERROR 22-322: Syntaxfehler, erwartet wird eines der folgenden: eine Zeichenkette in Hochkommata, ',', AS, FORMAT, FROM, INFORMAT, INTO, LABEL, LEN, LENGTH, TRANSCODE.

Zeile 4.310: ERROR 202-322: The option or parameter is not recognized and will be ignored.

Zeile 4.319: ERROR 79-322: Erwartet wird ')'.

Zeile 4.319: ERROR 76-322: Syntaxfehler, Anweisung wird ignoriert.

Thank you very much

Re: proc sql md5 and oracle-db

F_Röder — Wed, 16 Aug 2017 06:56:40 GMT

I have forgotten:

I think - this is no oracle problem - just a sas problem

Re: proc sql md5 and oracle-db

LinusH — Wed, 16 Aug 2017 08:23:30 GMT

You still have a syntax error, you can't have "as key format=$hex32." and the continue with the case statement.

the as... part must come at the very end of the column definition.

But as I understand the latest version of the code the as key is obsolete, your are using report_path as the en result of your columns definition.

When you get these type of syntax errors, start with simplifying the code until it works, then add things step by step.

Re: proc sql md5 and oracle-db

Patrick — Wed, 16 Aug 2017 09:02:39 GMT

@F_Röder

Below the code part which creates the md5() value using correct syntax. What's left is to pull your input string appart and recombine it but I believe you'll manage this on your own.

data have;
  str='/User Folders/REAL_USERNAME/Eigener Ordner/documentation.pdf';
  output;
run;

proc sql;
  create table want as
    select
      put(md5(scan(str,2,'/')),$hex32.) as want
    from have
    ;
quit;

In case source and target table are both in Oracle then I would use explicit pass-through Oracle SQL code. There are Oracle functions which can do the same than what you're doing here in SAS.

Re: proc sql md5 and oracle-db

F_Röder — Thu, 17 Aug 2017 10:30:48 GMT

Thank you very much for your help.

This works great:

put(md5(scan(str,2,'/')),$hex32.) as want

Also concatenating this with other strings - something like that:

'FIRST_STRING' || put(md5(scan(str,2,'/')),$hex32.) || 'THIRD_STRING'

Thank you very much.

Best regards

Frank