BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
F_Röder
Calcite | Level 5

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

 

 

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Patrick
Opal | Level 21

@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

8 REPLIES 8
LinusH
Tourmaline | Level 20
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
F_Röder
Calcite | Level 5

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

 

 

LinusH
Tourmaline | Level 20
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
F_Röder
Calcite | Level 5

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

F_Röder
Calcite | Level 5

I have forgotten:

 

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

 

 

Patrick
Opal | Level 21

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

LinusH
Tourmaline | Level 20

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
F_Röder
Calcite | Level 5

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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