SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Help create physical table from metadata in sas code, i.e. no "clicking" in DI-Studio etc.

Reply
Occasional Contributor KJD
Occasional Contributor
Posts: 6

Help create physical table from metadata in sas code, i.e. no "clicking" in DI-Studio etc.

I have a number of tables defined in metadata that doesn't exist as physical tables.

How do I create the physical tables using sas code from metadata?

(I don't want to do it manually...)

SAS Employee RMP
SAS Employee
Posts: 52

Create physical table from metadata in sas code

What do you mean by "manually"? Are you using DI Studio? If so, right clicking on the table and selecting "open" will create an empty table for you. Or you can run your jobs which during the load step will create the required physical table.

New Contributor
Posts: 3

Create physical table from metadata in sas code

I'm also interested in how to do this.

That is: How do I perform the "right clicking on the table and selecting open will create an empty table for you" action programatically using SAS Base code?

PROC Star
Posts: 7,366

Re: Create physical table from metadata in sas code

JJosh,

There are a number of ways to do what you ask, but the question really ought to be asked and answered in a separate thread.

Occasional Contributor KJD
Occasional Contributor
Posts: 6

Create physical table from metadata in sas code

Hi art297, I might have been a little unclear in my question but what JJosh asks is exactly what I want to know.

Would be grateful for some tips.

PROC Star
Posts: 7,366

Create physical table from metadata in sas code

Since you are both asking the same question, I'd be glad to make my suggestion here.  It actually has two parts.

First, the easiest way I know to create an empty file that has all of the properties of an existing file is to use proc sql.  Specifically:

proc sql;

   create table want

    like have

  ;

/*or a working example:*/

proc sql;

   create table want

       like sashelp.class

  ;

quit;

Of course there are other ways to do the same thing, but the above will do it.  Now, how to attach code like that so that you can select that option when you right click on a file, take a look at:

http://www.sascommunity.org/wiki/Tips:Making_any_repeatable_task_a_file_related_menu_item

It is really a quick and easy and extremely versatile method one can use to automate virtually any task.

Occasional Contributor KJD
Occasional Contributor
Posts: 6

Create physical table from metadata in sas code

Thank you art297 but my question concerned how to create tables that are defined on the MetaData within the Intelligence platform enviroment without using DI-Studio or such. Tables defined in metadata (e.g. defined in DI-studio) but not created as physical does not turn up in the sashelp dictionary.

PROC Star
Posts: 7,366

Create physical table from metadata in sas code

That is why I originally wanted JJosh to post the question in a separate thread.  I haven't worked with the intelligence platform as yet, thus don't know how it stores metadata.  However, I would think that it has to be stored in a file and, as such, should be accessible.  The method I suggested is not dependent upon dictionary views.

Occasional Contributor KJD
Occasional Contributor
Posts: 6

Create physical table from metadata in sas code

Hi RMP, by manually I mean -right clicking on the table and selecting "open"-.

SAS Employee
Posts: 36

Help create physical table from metadata in sas code, i.e. no "clicking" in DI-Studio etc.

As others have said, there are a number of ways to do this.

1.  If you have the table defined in metadata, create  a new job, drop the table as a target on your job, add a Loader transform, and drop any source table that already exists as the source to the loader;  something like this :  source table -> loader -> target table

Run the job, the target table will be created, and the source data will be loaded into it.  DI Studio will also create any keys or indexes that you have defined on your target table for you as part of that load process.  DI Studio will generate all of the appropriate code to create the table, columns, etc that you defined in metadata. 

There are also various load techniques available in that loader, you can try various settings.  Base SAS tables typically should be replaced but you may want truncate for database tables. 

2.  IF you are working with BASE SAS tables only, there is a nice feature in the data viewer that will actually create the base sas table if it doesnt exist and put you in edit mode so that you can begin adding data to it.  To do this, RMB on the Base sas table that does not exist, and say "Open".  If the physical table does not exist it will tell you that, ask if you want to create it.  Say yes and the table will get created as an empty table in the library where you selected the table to go to in the metadata definition.  The nice thing about this technique is it is essentially a shortcut way of creating base sas tables.  The same code that the loader generates is generated on the fly to create the table.  This only works for base sas tables. 

Super User
Posts: 5,260

Help create physical table from metadata in sas code, i.e. no "clicking" in DI-Studio etc.

Yes, the second is nice and quite helpful, I have used it a lot.

But if one could put something on a christmas wishlist, it would be more/better support for forward engineering:

  • generate the code that performs the step in bullet no 2. This without having to create a job first.
  • Possibility to create a change/update script, which is based on differences between metadata and physical table structure. I think we spend too much time on manually genererate update scripts for database changes. It should be possible to implement, the information is there!

/Linus

Data never sleeps
SAS Employee
Posts: 1

Re: Help create physical table from metadata in sas code, i.e. no "clicking" in DI-Studio etc.

I feel as if people are missing the point here. The original question was how to do this programmatically. That is, a generic program (probably a macro) that will create a table from its metadata. I know how to do it, as I'm sure many others do. But it's a bit laborious, so I'm assuming that the original poster was looking - as I was - for somebody who'd already done the work! If I don't find it anywhere else, I'll write the code, and post back.

Keith

Ask a Question
Discussion stats
  • 11 replies
  • 1375 views
  • 0 likes
  • 7 in conversation