topic Write Base64 data to file directly from SQL server in SAS Programming

Write Base64 data to file directly from SQL server

heffo — Tue, 07 May 2019 02:24:55 GMT

I have some data in an SQL server database, some of the data is longer than 32767 characters. I would like to save this data to individual files.

I know how to do it in SAS when the data is there

*Write the data to file. Then remove the variable that contains the data but keep the variable that contains the path to the file.;
data _null_;
    set work.fileAttachement;
	length filePathName $500.;

	*Set the file path!;
	filePathName = cats("C:\temp\",Filename);

	file dummy filevar=filePathName;
	*Convert the data from Base64 to binary. NB! This does not work when the file is larger than 32 767 chars... :-(;
	body = input(body,$BASE64X32767.);
	put body; *Print the actual content to the file.;
run;

The problem is that most of the files are too long. So, the question is, how can I write the Base64 data directly to file from the SQL step where I extract the data (using proc sql and "connect to odbc as ...". BTW, one of the variables in the data set contains the actual file name that I want to use.

Re: Write Base64 data to file directly from SQL server

Tom — Tue, 07 May 2019 04:57:00 GMT

Why do you think you need to use PROC SQL?

Just make a libref pointing to your source database system.

libname mydb odbc ..... ;
data _null_;
    set mydb.fileAttachement;
    ...

Re: Write Base64 data to file directly from SQL server

heffo — Thu, 09 May 2019 00:16:31 GMT

I did try that way, after you told me, but it only reads 32 767 chars. 😞

I found some code that can split a large string into substrings (around 900!) but unfortunately, that does not seem to be working. I suspect that it might be because it is saved in the wrong format (if i get smaller files and use the format $BASE64X32767. it works). So I'm still on page one on this one...

Re: Write Base64 data to file directly from SQL server

Tom — Thu, 09 May 2019 00:19:27 GMT

Sounds like the problem is that the fields in your database are longer than SAS allows for character fields.

So in that case you need to use pass through SQL (or have your DB admin make you a view) that can parse the long fields into multiple shorter fields.

But even then you could create a ViEW using PROC SQL and then just normal data step/proc step to process the view.

Re: Write Base64 data to file directly from SQL server

Tom — Thu, 09 May 2019 00:37:02 GMT

Show how the table (or at least the fields you are pulling) is defined in the database.
Is it one field? Multiple fields? Are they VARCHAR? CLOB? Something Else?
What options does the language of the database provide for splitting the values into pieces? Does SUBSTR() or SUBSTRING() work on those data types?