BookmarkSubscribeRSS Feed
deleted_user
Not applicable
Hello!

I'm designing a transformation which replaces target table and creates index on it. Target table, that is connected to output port of transform has defined index, but I can't write the "create index" statement in the transformation, because there is no any information about indexes in macro variables, automatically generated in my transformation.

How can I get information about index on target table from transform?
4 REPLIES 4
LinusH
Tourmaline | Level 20
If you want to rely on what definitions resides in metadata, you'll probably have to connect to the metadata server and retrieve the index spec using the functions metadata_getnobj, metadata_getattr and metadata_getnasn.

/Linus
Data never sleeps
Patrick
Opal | Level 21
"...a transformation which replaces target table and creates index on it"...

Why can't you use a table loader using a "replace" option?
deleted_user
Not applicable
Patrick, source and target tables are both stored in relational database, and table loader passes data through SAS, and this causes extra data transmissions between servers. My transform only passes "create table as select * ..." to database.
Patrick
Opal | Level 21
Igor

I'm not 100% sure that if source and target are in the same DB that the code generated by the "Table Loader" results in data passing via the SAS Server from Source to Target.

A lot of SAS code is translated into DBMS specific SQL during SAS execution. I would know the option for Oracle to get in the SAS log what's really sent to the DB for execution (what DBMS are you using?).


Another approach would be to use a "SQL Join" transform.

I did the following test:

1. Job with Source and Target table (index on target table)
2. "SQL Join" transform
3. Connect Source to "SQL Join"
4. Delete output table on "SQL Join" (this green little table on the right side of the transform).
5. Connect "SLQ Join" to Target
6. In "SQL Join" designer: Delete "Join" object.
7. In "SLQ Join" designer: Connect Source to "Select" object

The resulting code looked good to me. Both the PROC SQL part creating and loading the target table and the PROC DATASETS part creating the index after the data gets loaded looked fine to me. The code will execute on the DB.


I believe to use a "User Written Code" node or to create a "User Tranformation" should be the last measure if there are no other means to solve a DI challenge.
.....I already created quite a lot of both of these objects.... 😉

In your case: It's not only about indexes but also about constraints. The only way I can think of to create this manually is to query the metadata and then based on the result to create the code on-the-fly. This would be quite a task.

HTH
Patrick

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 1401 views
  • 0 likes
  • 3 in conversation