DATA Step, Macro, Functions and more

Add column to a physical table, after adding to its metadata.

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 7
Accepted Solution

Add column to a physical table, after adding to its metadata.

I have added a new column using table properties in DIS and defined description, length, format and informat.

How to update physical table by metadata in case that the table is not empty and already have some obs?

Is there any other way but rewrite it by itself?

data glhf;

     set glhf;

     attrib gl label = "good luck" length = $9;

     gl = 'good luck';

run;


Accepted Solutions
Solution
‎10-31-2012 11:47 AM
Super Contributor
Posts: 474

Re: Add column to a physical table, after adding to its metadata.

I'm not aware of the context of your question, but adding a column to a table should always require working from source to top, adding first this column to the previous tables/files in the process.

Still, would a piece of code that you could run from DIS (say in a source editor window or pre-process) and automatically will refresh the layout of a specified table base on metadata, suit you?

Cheers from Portugal.

Daniel Santos @ www.cgd.pt

View solution in original post


All Replies
Valued Guide
Posts: 634

Re: Add column to a physical table, after adding to its metadata.

Are you more concerned about preserving the attributes of GL?  If so specifying the ATTRIB after the SET will not change the length but could change the label.

Are you instead trying not to overwrite the value of GL if it already exists on the incoming data set?

Super User
Posts: 19,772

Re: Add column to a physical table, after adding to its metadata.

You can try SQL instead but I'm pretty sure it recreates the table anyways, just in the background.

Add a column and update

http://support.sas.com/documentation/cdl/en/sqlproc/62086/HTML/default/viewer.htm#a001395096.htm

Update from a table (link from the above page)

http://support.sas.com/documentation/cdl/en/sqlproc/62086/HTML/default/viewer.htm#a001392966.htm

Occasional Contributor
Posts: 7

Re: Add column to a physical table, after adding to its metadata.

@Art Carpenter

I do not try to overwrite the value of GL. GL is a new column.

--------------

I'll try to explane on next example:

Imagine, that we have a next table GLHF registered in metadata:

data GLHF;

     do n = 1 to 5;

          D = 'do not';

          W = 'worry';

     output;

     end;

     drop n;

run;

proc print; quit;

Now we want to add a new column B with the lable "Be happy". And we do it in the table's metadata using DIS.

If we try to open this table in DIS - we will recieve an error "ERROR: Column B could not be found in the table/view...".

The question is - how to populate a new column B to existing table GLHF with all attributs, that we have set in the metadata?

Simply - we need to rewrite a table duplicating all metadata attributs in data step:

data glhf;

     set glhf;

     attrib b label='be happy';

     b = '';

run;

Somebody know other way? Because if you need to add 10 columns with diffetent attributs - duplicate there all in code can be... idly.

(I have a feeling the answer "Add columns by code and then update table metadata by DIS" ^_^).

Super User
Posts: 19,772

Re: Add column to a physical table, after adding to its metadata.

You can't update the metadata without updating the table....

The metadata describes the table and if you change it without changing the table then they don't match and you'll get errors. So yes, add columns via colde and update the columns via code (you won't be able to add the values either by DIS I believe). THEN update the metadata.

Solution
‎10-31-2012 11:47 AM
Super Contributor
Posts: 474

Re: Add column to a physical table, after adding to its metadata.

I'm not aware of the context of your question, but adding a column to a table should always require working from source to top, adding first this column to the previous tables/files in the process.

Still, would a piece of code that you could run from DIS (say in a source editor window or pre-process) and automatically will refresh the layout of a specified table base on metadata, suit you?

Cheers from Portugal.

Daniel Santos @ www.cgd.pt

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 629 views
  • 3 likes
  • 4 in conversation