About Hercules

Hercules · ‎12-20-2015

Thanks Patrick for spending your valuable time while reading my paper. Being a Data modeler I completely agree with your point that DDL should be created from Data ERwin Modeler. But need of the hour is to automate a system which can save time and money. What I am actually doing here is not playing around with any transformation, but to migrate the data from source to landing area from where we can clean and transform the data to migrate it into the staging area. I am more focusing on creating the DDL for the tables. Now, for every table, the developer has to execute the query to identify the DDL at the source, and if I have 38 sources with 1000 tables in each source, then the developer has to manually fire the query for total of 38X1000 tables, copy its DDL, change it according to Teradata (Target) Database and then execute queries for all tables likewise. Likewise, he/she has to do this for rest of the schema i.e. Landing, Staging, Error, Archive, Incremental, etc. As you can see this is time consuming, I proposed a way to eradicate the manual intervention of developers and make this an automated system. This is what I have implemented in my project also and it has saved us a lot of time and implementation cost. I appreciate your feedback. Please let me know if I am not able to justify my answer. And I apologize for my late reply. Thanks.

Hercules · ‎12-20-2015

My apologies for the late reply. And Thanks for reading the paper. 1 - Is it possible to filter out records when reading from the source Ans: Yes you can add one more column to the deriving table and name it as FLTR_COLMN at table level. Then you can select the column along with table name in a macro variable and paste it in the proc sql; insert into claw. 2 - Is it possible to handle joins in the macro itself. Ans: For hadling join you have to make a new table with columns: SRC_TAB, TGT_TAB, JOIN_TYP (in which you have to specify inner, left, right join) and CONDTN column. Storei it in a macro and call it.

Hercules · ‎11-17-2015

Hello, Sorry if the post is irrelevant to the forum but I really need some guidance. I've written a SAS paper on Data Warehouse dynamic implementation which has reduced the time and implementation cost in my project also and I received an award for it. Hence it is tested and live. I was thinking to write a paper on it describing the process and by sharing the source code so that anyone can use it in their projects. I have posted it in sascommunity.org as an article before submitting it to get it published, as this is my first time that I'm formating a technical paper. I would be grateful if you can give your honest feedbacks about it and throw some light on my week parts. Find the link below http://www.sascommunity.org/wiki/%25UniCreate:_A_SAS_Macro_for_Enterprise_Data_Warehouse_Dynamic_Implementation

Hercules · ‎09-25-2015

Thanks Chris for the reply. And sorry I didn't provided the full information. Basically I have written a SAS program which is mainly used for ETL purpose in Data warehousing projects. What it does is, it can create DDLs of 1000s of tables in Teradata database and load them simultaneously by connecting from multiple sources like Oracle, MySQL, DB2 and MS SQL Server at one execution. It has saved efforts and time from a year of manual and numerous SAS DI jobs development to just couple of weeks. Basically, it has automated the ETL process of our project. For this my organization has recognized me with a SPOT award. Therefore, I was thinking to get the program published by making a proper paper with abstract, introduction, flow of the code, etc. But I have no clue how to get it done. Thanks.

Hercules · ‎09-25-2015

Hello everyone, Can anyone please guide me about how to publish a paper on a SAS program? Thanks.

Hercules · ‎07-31-2015

Yup got it. It's done. In those data set by using set statement of my table. Also had to make users in Linux as well, did it by using X command inside the macro. Now it's Parameterized. :smileygrin: Thanks Jaap

Hercules · ‎07-30-2015

I was just reading the document which you've shared earlier in your previous thread. And I was getting close to it. Was about to ask that if I want to leave a table blank such as phone table and email table, what repercussions would it have. And then your reply came at that very moment. II shall try this tomorrow and will get back to you. Thanks a lot for the help Jaap, as always.

Hercules · ‎07-30-2015

Sorry sbb, I lost you. For your previous query. Yes it has been confirmed from the admin that this is what he wants from me as there are 10000 users needs to be created for a task, which is practically not possible manually from SMC. And on further note, that table is locked for all user and is only accessible by the DBA. I just want to know how can I create users from Base SAS? Can you please help?

Hercules · ‎07-30-2015

Haha.. Yes. Can you tell me how to create users through sas code?

Hercules · ‎07-30-2015

Hello all, I have a table with three column. Column 1->User_Name Column 2 -> User_Password Column 3 -> Description I want to make a SAS script through which users will create without SMC. I have gone through some threads and documents which has shared and got an idea that it has to be achieved by some macros. Example %mduextr, %mducmp, etc. But can't relate how to play with it. Can somebody explain it to me? Has anybody done it before?

Hercules · ‎05-18-2015

First of all thank you everyone for taking out their valuable time and help me out to solve my problem. Thanks Tom for giving me an idea to deal it on Oracle level by specifying another column of length. I am not making that space value column as my UPI in landing, but the UPI in fact in landing is a REC_ID column which is a surrogate key. UPI for that column has been made in the staging area. Pardon me if I did something wrong as I am a fresher. I solved the problem by executing a simple pass through query. This is how I did it. Correct me if I am wrong but for now the problem has been solved and if anyone wishes the same to replicate the data from the source to the target as the way it was stored in source then one may follow this: Problem: Trailing spaces in a column at Oracle source end but not coming in target Teradata table. Solution: 1. Pass through in Oracle, replace space by '$', insert into Teradata target table. 2. Again pass through in Teradata, Write an update query to replace '$' with spaces. proc sql; connect to oracle(......); insert into tera.tbl1 select * from connection to oracle (select col1 , replace(col2,' ','$') as col2 , col3 from schema.tbl2 ); disconnect from oracle; connect to teradata(.....); execute( update schema.tbl2 set col2 = oreplace(col2,'$',' ') )by teradata; execute(commit); disconnect from teradata; quit;

Hercules · ‎05-17-2015

Thank you Jaap for the explanation. At least the picture is clear to me. And yes, I do know that Teradata is an OLAP database, having an architecture of amps and Mvps, which obviously makes it a good database for Data warehousing whereas Oracle is an OLTP on the other hand. I understood that SAS has characters which by default pads the spaces to its maximum length. And the answer to your question is,I want two separate values, one with the space and one without space because I want to replicate the data from source to landing in teradata, which means ideally the data should be as it is in the source. And then I want to carry that data followed by cleaning to the staging area. I also do understand the concept of primary index differ between the two. I know how when we execute a query, the query spreads on multiple amps, retrieve the data and then show it to us. But what I am doing here is that I am making the column as a UPI and not just the PI, so that no two values should be equal in that column. But if it can not be achieved I have to look for an alternate solution. But thanks to all for their valuable time.

Hercules · ‎05-17-2015

And yes.. I forgot to mention that both my source and target column are Varchar. In character I can understand that its an universal behaviour, but Varchar occupy only the string length and not more than that, leaving the rest of the allocated buffer as not space but null.

Hercules · ‎05-17-2015

Ok so the whole scenario is, I have a table in Oracle. I want to load its data in Teradata through SAS. In Teradata, one of the column of my target table is a Unique Primary Index. Now in oracle my source column has a value as 026 and 026 with a trailing space. When I load the data in Teradata, beacause the target column is a UPI, it discards the other 026 with the space. I want both 026 values. How can I achieve that. What I don't understand, why 026 and 026 with a space means same to SAS.

Hercules · ‎05-17-2015

No Xia, Its still not working. For eg: If my data set is; data saslib.tmp; length a $ 20; a='anant'; output; a='anant';output; a= 'anant ' ;output; run; And I am using: data test; set saslib.tmp; chck=anyspace(a); keep a chck; run; Then the output is: anant 6 anant 6 anant 6 which ideally should be : anant 0 anant 0 anant 6

Online Status	Offline
Date Last Visited	‎10-13-2017 02:55 AM

Re: A SAS Macro for Enterprise Data Warehouse Dynamic implementation

Re: A SAS Macro for Enterprise Data Warehouse Dynamic implementation

A SAS Macro for Enterprise Data Warehouse Dynamic implementation

Re: Publish a Paper with SAS

Publish a Paper with SAS

Re: Create SAS users through Base SAS Program

Re: Create SAS users through Base SAS Program

Re: Create SAS users through Base SAS Program

Re: Create SAS users through Base SAS Program

Create SAS users through Base SAS Program

Re: Unable to use teradata or sql functions in table loader expression...

Re: A SAS Macro for Enterprise Data Warehouse Dynamic implementation

Re: A SAS Macro for Enterprise Data Warehouse Dynamic implementation

A SAS Macro for Enterprise Data Warehouse Dynamic implementation

Re: Publish a Paper with SAS

Publish a Paper with SAS

Re: Create SAS users through Base SAS Program

Re: Create SAS users through Base SAS Program

Re: Create SAS users through Base SAS Program

Re: Create SAS users through Base SAS Program

Create SAS users through Base SAS Program

Re: Don't want to remove trailing spaces

Re: Don't want to remove trailing spaces

Re: Don't want to remove trailing spaces

Re: Don't want to remove trailing spaces

Re: Don't want to remove trailing spaces