topic Help: how to update (only adding columns) a table in the metadata (SAS9.3) in SAS Data Management

Help: how to update (only adding columns) a table in the metadata (SAS9.3)

Brigitte_sas — Tue, 12 Feb 2013 08:21:48 GMT

Hi there,

I need your help: I have to update the metadata for a table with base program code (SAS9.3). I should only add the attribute for columns to the metadata. It's not allowed to delete descriptions from old columns although if this old columns no more exist in the physical table. what's the procedure for resolving this problem?

Thank you very much for your support

Brigitte

Re: Help: how to update (only adding columns) a table in the metadata (SAS9.3)

LinusH — Tue, 12 Feb 2013 10:20:20 GMT

Please help me out on what you are trying to achieve, and why?

What does your environment look like, do you use DI Studio, or just SMC as metadata tool?

Can't really picture the scenario, is that you need to make new columns available in a data mart for end users, because of some kind of dynamic transpose of data?

Re: Help: how to update (only adding columns) a table in the metadata (SAS9.3)

Brigitte_sas — Tue, 12 Feb 2013 12:48:22 GMT

Hi LinusH,

I've only the SAS Management Console and SAS Enterprise Guide for reprocessing the data.

I preassign the library with the Metadata LIBNAME Engine Statement:

libname lib1 meta library="DBS" metaout=data;

We extract data from oracle and transpose this data. The final dataset I have to update in the metadata so that the user can work with the SAS Enterprise Miner with this dataset. So we have no historical data and need all the columns (the old one and the newest) just in case we have to run an old Mining process.

Best regards,

Brigitte

Re: Help: how to update (only adding columns) a table in the metadata (SAS9.3)

Haikuo — Tue, 12 Feb 2013 14:09:44 GMT

There are two ways I know of to updata (sync) your metadata:

1. Manual way: if you don't do it on a regular basis and there aren't many of them, you can use MC:

Plug-ins -> Data Library Manager, right click your target table, then select "update metadata..."

2. Proc Metalib. It is often used in batch update via automated process. Use EG to do the update.

Haikuo

Re: Help: how to update (only adding columns) a table in the metadata (SAS9.3)

LinusH — Tue, 12 Feb 2013 14:11:58 GMT

I would take a look at PROC METALIB, seems fairly easy.

SAS(R) 9.3 Language Interfaces to Metadata

Re: Help: how to update (only adding columns) a table in the metadata (SAS9.3)

Brigitte_sas — Fri, 22 Feb 2013 19:41:45 GMT

Hi There,

thank you for your answers. proc metalib is very well and fine, but it is not enough! When I update the metadata I will hold the metadata information about the deletes columns AND additionaly the information about the new columns.

I think I have to look for a workaround on physical level.

Thank you very much for your proposals.

Cheers,

Brigitte

Re: Help: how to update (only adding columns) a table in the metadata (SAS9.3)

Haikuo — Fri, 22 Feb 2013 20:23:53 GMT

I finally understand what you want, but you lost me right there. So basically you need a metadata that will hold both deleted and existing columns information. Why? what is the point to have a metadata that does not match what you have in the data source? If you have a constantly changing data source, then you need to update (sync) your metadata just as often. You can choose to run Proc metlib in batch on regular basis (we do it daily at middle night) or on the fly every time before you use it.

Just my2cents.

Haikuo