topic Need help extracting BLOB image from DB table and embed it on PROC Report in SAS Programming

Need help extracting BLOB image from DB table and embed it on PROC Report

meatballs12345 — Thu, 02 Jan 2025 08:13:08 GMT

Hi SAS Community!

This is my first time working with image column in SAS EG. Now, I need help in extracting an Image (BLOB Jpeg Image) from the DB table and have it embedded on an HTML report.

The user must be able to download the image once it's clicked on the HTML Report.

What I have done so far:
-Tried to download on unix server and reference the images on the proc report, but users doesn't have access on the unix server path in order to download.

Here's what I researched, please correct me if I am wrong since I'm really lost right now:

-The column IDPIC value starts with something like this (sample=FFD8FFE000104A4649460001010000480048) which is in hexadecimal format.

-I don't need to download the image on my local (Unix server) for me to embed it to HTML.

-From the hexadecimal format, I need to convert it into RAW binary format first (sample=ÿØÿàJFIFHHÿáExifMM*JR(iZHH ö Ñÿí8Photoshop3.08BIM8BIM%ÔÙ²éìøB~ÿâ)
before converting it into base-64 (this is the format that can be embedded on PROC Report)

-SAS EG doesn't have a built in base64 encode function.

Here's my code:

DATA TBL_FOR_PROC_REPORT;

SET SOURCE_TBL;

length IMG $32767 clean_hex $32767 stable_hex $32767 binary_data $32767 i 8 hex_byte $2;

IF NOT MISSING(IDPIC) AND NOT MISSING(REFNO) AND NOT MISSING(SNAME) THEN DO;

clean_hex = compress(IDPIC, ' ');

stable_hex = TRANWRD(clean_hex, '0A'x, '');

stable_hex = TRANWRD(stable_hex, '0D'x, '');

stable_hex = compress(stable_hex, , 'kw');

binary_data = '';

/* This is where the conversion fails */

DO i = 1 TO LENGTH(stable_hex) BY 2;

hex_byte = substr(stable_hex, i, 2);

binary_data = catx('', binary_data, byte(input(hex_byte, $HEX2.)));

END;

/* This column shows corrupted image on proc report later on */

IMG = 'data&colon;image/jpeg;base64,' || PUT(binary_data, $BASE64X32767.);

END;

ELSE DO;

IMG = 'No ID';

END;

DROP i hex_byte clean_hex;

RUN;

Your replies would be of great help! Thank you! 😊

Re: Need help extracting BLOB image from DB table and embed it on PROC Report

ChrisNZ — Fri, 03 Jan 2025 05:23:52 GMT

1. I don't see why you need an in-line image for users to be able to download it. If you can save the HTML file somewhere they can see, you can also save an image file there.
2. If you need an in-line image, see this alternative method https://blogs.sas.com/content/sgf/2017/04/21/use-sas-send-email-embeds-graph-body-email/
3. If you need a base64 image, I'd first create the image file so you know your blob extraction works. Then you can use the INLINE option of ODS HTML5. https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/odsug/p0hcv8gpxqebnpn1is52we2enltx.htm

Re: Need help extracting BLOB image from DB table and embed it on PROC Report

JMANGUERRA — Fri, 03 Jan 2025 07:38:51 GMT

that's awesome

Re: Need help extracting BLOB image from DB table and embed it on PROC Report

meatballs12345 — Mon, 06 Jan 2025 06:01:54 GMT

Hello, the requirement is to embed the image on the excel report that's supposed to be generated when the "export to excel" link was clicked on the SAS HTML Report.
Also, if my understanding is correct, does that mean I have to download the image elsewhere first?