10-21-2011 07:14 AM
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...)
10-22-2011 03:59 PM
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.
10-24-2011 11:19 AM
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?
10-25-2011 09:16 AM
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:
create table want
/*or a working example:*/
create table want
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:
It is really a quick and easy and extremely versatile method one can use to automate virtually any task.
10-25-2011 09:37 AM
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.
10-25-2011 09:54 AM
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.
11-22-2011 02:46 PM
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.
11-23-2011 05:31 AM
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:
04-18-2012 09:32 AM
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.