topic Re: SAS 9.4 Extract long texts from SAS Metadata TextStore in SAS Programming

SAS 9.4 Extract long texts from SAS Metadata TextStore

ErikLund_Jensen — Wed, 18 Jan 2017 19:51:55 GMT

Hi everybody

We run a traditional data warehouse with several thousand jobs built in DI Studio and a lot of stored processes. There is a lot of user written code stored in SAS metadata, and we want to extract the SAS source code from SAS metadata TextStore and store it in disk files, which makes it much easier to scan for used macros, file names etc. There are about 2000 entries containing user written code from DI Studio transformations, pre- and post-process code and code used in Stored processes.

I use an automated process flow that extracts metadata to XML, reads the xml file into a SAS data sets using a XML MAP and - in a flllowing data step - formats the StoredText content to SAS programs and writes each entry to a disk file with the TextStore ID as file name. The link between files and jobs/transformations are maintained in tables, which are written along with the extraction.

It works very well. But some entries are longer than 32k, and they are truncated when read into the SAS data set. There seems to be no workaround like using the XML map to split the long texts in several records, so I have not figured out how to get content beyond the 32k limit out in disk files.

I hope someone has a good idea to solve this problem.

Re: SAS 9.4 Extract long texts from SAS Metadata TextStore

ChrisNZ — Wed, 18 Jan 2017 22:18:15 GMT

Sadly I can't offer help, but SAS should seriously work on lifting the limits for character variable length and metadata length.

The increase from 200 to 32k is 20 years old.

Re: SAS 9.4 Extract long texts from SAS Metadata TextStore

LinusH — Thu, 19 Jan 2017 06:59:37 GMT

Step one: fire the ETL designer/development lead for allowing that amount of user written code. As you experience now, it's a damage that is costly to repair.

For the XML, since the XML mapper nor Base SAS handles this, you probably need to use a 3rd party tool to do this for you.

I haven't dug i to Metadata behind the scenes that much, but it's stored in Base SAS tables, so I guess that the original store would be splittede into multiple rows...or. ..?

Re: SAS 9.4 Extract long texts from SAS Metadata TextStore

ErikLund_Jensen — Mon, 23 Jan 2017 16:56:08 GMT

Thanks to LinusH !

Stupid I did'nt think of that myself. Of course the texts can be extracted from Metadata tables, and for my purpose a libname to the most recent backup is sufficient. I ran into a problem with transcoding danish national characters from Unicode, but that can be solved.