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...)
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.
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?
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.
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.
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.
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.
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.
Hi RMP, by manually I mean -right clicking on the table and selecting "open"-.
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.
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:
/Linus
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
Hi KJD,
I needed this today as part of a new Data Controller feature, so I wrote a macro for it which you can find here: https://github.com/macropeople/macrocore/blob/master/meta/mm_createdataset.sas
To use:
/* import library */
filename mc url "https://raw.githubusercontent.com/macropeople/macrocore/master/macrocore.sas";
%inc mc;
/* run using libref.ds syntax */
%mm_createdataset(libds=LIBREF.DATASET)
/* run using meta uri */
%mm_createdataset(tableuri=G5X8AFW1.BE00015Y)
This will create a table in WORK with the relevant names, labels, lengths, formats and informats.
Hi @KJD
It is a lot of work, but not impossible. Something like:
I don't have a program that does exactly that, but I have programs to copy/paste from, so I might find time to put something together for you between christmas day and new year's eve, even if my wife has strong opinions on my working in holidays.
But before I do it, I would like to hear why it is important for you to have it done programatically, and also why you want to do it at all. I would think empty tables are as useless as missing tables, so I am really curious about why you want to create them in advance and not wait until you have a program to load them with data.
The SQL Delete transformation creates a physical table from metadata if it doesn't exist.
If you don't want to delete anything if the table exists then add a NOEXEC under "Additional SQL options:"
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.