BookmarkSubscribeRSS Feed
KJD
Fluorite | Level 6 KJD
Fluorite | Level 6

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...)

14 REPLIES 14
RMP
SAS Employee RMP
SAS Employee

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.

JJosh
Calcite | Level 5

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?

art297
Opal | Level 21

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.

KJD
Fluorite | Level 6 KJD
Fluorite | Level 6

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.

art297
Opal | Level 21

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.

KJD
Fluorite | Level 6 KJD
Fluorite | Level 6

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.

art297
Opal | Level 21

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.

KJD
Fluorite | Level 6 KJD
Fluorite | Level 6

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

nar_sas
SAS Employee

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. 

LinusH
Tourmaline | Level 20

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
KeithAdamsAtSAS
SAS Employee

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

AllanBowe
Barite | Level 11

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.

/Allan
SAS Challenges - SASensei
MacroCore library for app developers
SAS networking events (BeLux, Germany, UK&I)

Data Workflows, Data Contracts, Data Lineage, Drag & drop excel EUCs to SAS 9 & Viya - Data Controller
DevOps and AppDev on SAS 9 / Viya / Base SAS - SASjs
ErikLund_Jensen
Rhodochrosite | Level 12

Hi @KJD 

 

It is a lot of work, but not impossible. Something like:

 

  1. Write XML to extract table information including dataset label, columns with all attributes and physical path to the library.
  2. Write MAP to transform the extracted information to a SAS Data set containing all information in a row pr. column (Directoryname, Physical table name, dataset label, column name, -label, length, type and format).
  3. Run the extract with proc metadata to create XML file with the result.
  4. Read data into sas with an XML libname using the result- and map files.
  5. Write a SAS program to create a distinct list of directories found in the metadata extract and loop over it to get a list of all sas7bdat-files in the directories.
  6. Join with the data set created from metadata and keep all metadata information from all tables not present in the list of physical tables.
  7. Write a SAS program to write and execute code to create these tables. 

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.

 

Patrick
Opal | Level 21

@ErikLund_Jensen@AllanBowe 

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:"

Capture.JPG

Capture.JPG

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

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.

Discussion stats
  • 14 replies
  • 5015 views
  • 4 likes
  • 10 in conversation