DATA Step, Macro, Functions and more

proc sql md5 and oracle-db

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 5
Accepted Solution

proc sql md5 and oracle-db

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

 

 

 

 

 

 


Accepted Solutions
Solution
‎08-17-2017 06:31 AM
Respected Advisor
Posts: 4,173

Re: proc sql md5 and oracle-db

[ Edited ]

@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.

View solution in original post


All Replies
Super User
Posts: 5,441

Re: proc sql md5 and oracle-db

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.
Data never sleeps
Occasional Contributor
Posts: 5

Re: proc sql md5 and oracle-db

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

 

 

Super User
Posts: 5,441

Re: proc sql md5 and oracle-db

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?
Data never sleeps
Occasional Contributor
Posts: 5

Re: proc sql md5 and oracle-db

Wow - very fastSmiley Happy

 

 

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

Occasional Contributor
Posts: 5

Re: proc sql md5 and oracle-db

I have forgotten:

 

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

 

 

Solution
‎08-17-2017 06:31 AM
Respected Advisor
Posts: 4,173

Re: proc sql md5 and oracle-db

[ Edited ]

@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.

Super User
Posts: 5,441

Re: proc sql md5 and oracle-db

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.

Data never sleeps
Occasional Contributor
Posts: 5

Re: proc sql md5 and oracle-db

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 154 views
  • 1 like
  • 3 in conversation