About strsljen

strsljen · ‎11-28-2018

Hi, Thanks for suggestion. I would rather not do so. It might bite me back when least expected. However, I found in Additional Options for transfotmation: Generate indexes on target tables and default is YES. When I set it to NO, I have no issues. I would be happiest if default option for it can be set when building User Written Transformation. Didn't find it, though.

strsljen · ‎11-27-2018

Hi, Im am working on simple User Written Transformation which calls Oracle stored procedures with some arguments. Some are INPUT and OUTPUT tables. But... SAS keeps generating %macro etls_createIndexes; function and tried to call it based on metadata information for input and output tables in regards to indexes. I really need SAS to leave them alone. Instead, SAS tries to create index that already exist and fails. How do I prevent this macro function to be autogenerated & called? Thanks! Best regards,

strsljen · ‎11-08-2018

Hi, These jobs are not independent although some of them can be ran in parallel. They all deliver set of tables which present a single data mart. So, at the end of the day, we need them as one flow, sharing the same macro variables (one of which is sequence key). Thanks for clarification. I will ask formally our sas support for confirmation for LSF and sessions. If that is "as designed" solution, we will have to handle it in a different way.

strsljen · ‎11-07-2018

Hi LaurieF, Thanks for you answer. I will look into it. Just one thought, though... How can I, that way, ensure that, if by any chance another instance of the flow gets executed, it doesn't mess with same variables (or overwrites them) coming from my original macros? Originally, I get sequence ID from Oracle sequence at the beginning and hold it through the flow. All jobs using value of that macro variable. If each job has to read value from any shared data set, there is always a chance it gets messed with. Best regards, Mario.

strsljen · ‎11-07-2018

Hi, I am trying to set a LSF flow with several SAS DI deployed jobs. Some of them I can run in parallel, some of them depend on other jobs from the flow. The problem is: LSF runs each and every job in the flow as a separate session. As a result of that, macro variables generated in first job are not visible in other jobs and this is mandatory condition for flow to run. (have no issues in DI Studio with it) Since LSF is my only option to schedule the flow with parallelism (where applicable), this behavior puts us back in sequential mode and thus increasing running time from estimated 1h30min to 4h. Anyone knows if it's possible to convince LSF to run jobs within the same flow in a single SAS session? Thanks! Best regards.

strsljen · ‎11-07-2018

Got it: one must log return code in own macro as soon as it happens, otherwise it gets back to 0 in DI code when code execution continues. So... Example: PROC SQL; CONNECT USING SEQLIB AS Oracle; execute (execute &SCHEMA_NAME..&PACKAGE_NAME..&STORED_PROCEDURE.()) by oracle; %let RES=&sqlrc; DISCONNECT FROM Oracle; QUIT; ss %if &RES > 0 %then %do; %end_with_error; %end;

strsljen · ‎11-06-2018

Hei, I am working on User Written Transformation in SAS DI Studio which will handle Oracle packages/stored procedures. In that sense, I am trying to catch when Oracle stored procedure raise an exception. When that happens, I can stop the flow, continue, or something else.. At the moment, I am unable to read from proc sql when stored procedure raises an exception, although I can see it in DI studio when job is running: - ERROR: ORACLE execute error: ORA-20020: This is a test application error / exception ORA-06512: ved "BUS_DATA_MAN_SANDBOX.DUMMY_PACKAGE", line 3 ORA-06512: ved line 1. Is there a way to read some SAS macro variable to determinate when this happens? Or to read some proc sql return code? I did try with &SQLRC and &job_rc, but these are 0 regardless. Thanks in advance! Best regards,

strsljen · ‎11-02-2018

Hi Angus, Thanks for your feedback. I would certainly do so from _INPUT, but this particular trasnformation has neither inputs, nor outputs, hence the issue with generating LIBNAME statement. Now.. I *could* add input to it, but it would be pretty misleadning to connect some random input table from the same schema I call stored procedure/package if has nothing to do with the whole process.. Best regards, Mario.

strsljen · ‎11-02-2018

Hi guys, Completely agree. Have not and will not hardcode anything I don't have to. I treid to get LIBNAME statement info based on that document and metadata, but got only error. The thing is: this transformation has to support being first transformation in DI job, meaning: no LIBNAME is pregenerated before it starts. That is the reason I need to find generic way to generate (not to hardcode) LIBNAME statement based on SAS LIBNAME parameter provided to it. I see what SAS DI generates in code in regards to LIBNAME; but fail to understand where he get it from and how can I do the same.

strsljen · ‎11-02-2018

Hi, I am working on User Written Transformation which can call Oracle package/stored procedure. PROC SQL; CONNECT USING SEQLIB AS Oracle; execute (execute MY_SCHEME.MY_PROCEDURE()) by oracle; DISCONNECT FROM Oracle; QUIT; The problem I am facing is how to generate correct LIBNAME string to assign proper library if I have LIBNAME as paremeter. My transformation uses no inputs/outputs. I tried: LIBNAME SEQLIB ORACLE PATH=&SERVER SCHEMA=&SCHEMA_NAME AUTHDOMAIN="Oracle.&SERVER..&SCHEMA_NAME" ; But not all of my defined libraries on SAS follow the same naming scheme for AUTHDOMAIN. As a result, this code works for only some of the libraries/Oracle schemes I use. I have also tried querying metadata server for that info as described: http://documentation.sas.com/?cdcId=pgmsascdc&cdcVersion=9.4_3.3&docsetId=lrmeta&docsetTarget=p08jvygbr7b40tn1y1t7yn1udrc3.htm&locale=en Error: 31 LIBNAME MARIO meta library=REPO_BIZ; ERROR: No metadata objects found matching the specified URI. ERROR: Error in the LIBNAME statement. Any suggestion how do I overcome this? Thanks in advance! Best regards,

strsljen · ‎10-17-2018

Hi, I would like to utilize Oracle sequence to get unique number within set of DI jobs running within the same flow. Could you reccommend the best way to utilize it within SAS DI? Basically I run: SELECT ORACLE_SCHEME.MY_SEQUENCE.NEXTVAL FROM DUAL Thanks! Best regards,

strsljen · ‎10-15-2018

@AngusLooney Thanks for this presentation. Now I am sure I am not the only one. If we ever meet f2f, beer is on me! 🙂 Trying to stop certain "influencers" in my company to convince the others that SAS DI is completely OK with bunch of User Written Code. Even some SAS experts in the field (tbh, old school SAS coders tend to disrespect "modern graphical tools of the trade") think it's no big deal. But it is. It would give us nightmare in maintenance later and I am fighting very hard against it. As for my original question, macro function might be a nice and clean solution. Good advice! Unfortunately, I am far from expert in writing SAS macro functions. Will try to get help or it will really take time. 🙂 Best regards,

strsljen · ‎10-15-2018

Hi, I need to add a row in existing Oracle table when SAS DI job starts (job name, timedate started, user who ran it...) What would be a proper transformation to do so? Most of them expect input table and I really need only simple INSERT INTO .... statement (would like to avoid user written code if possible). Thanks in advance! Best regards,

strsljen · ‎06-22-2018

Hi, Makes sense. Thanks! In the meantime, I checked RANK transformation and that does the trick - gives me ranking within parameters I need. Then I just take out rows with rank=1 for example. I completelly agree about DI Studio not being programming tool. It is possible to run all in user-written code but we are avoiding it by all means. Best regards,

strsljen · ‎06-19-2018

Hi, I have a use-case where table contains 1 or more rows for the same email address column. There is a columd "date". From all rows with same email address, only the one with newest date should be taken in a result table, regardless of values of other columns (20-ish columns are in the table all together). Is there a way to handle it in DI Studio without using custom written code? Thanks! Best regards,

Online Status	Offline
Date Last Visited	‎11-28-2018 08:02 AM

Re: Prevent %macro etls_createIndexes; in User Written Transformation ...

Prevent %macro etls_createIndexes; in User Written Transformation (DI ...

Re: LSF flow with several deployed jobs: how to run within a single SA...

Re: LSF flow with several deployed jobs: how to run within a single SA...

LSF flow with several deployed jobs: how to run within a single SAS se...

Re: proc sql: catching oracle exception

proc sql: catching oracle exception

Re: Assigning library in DI Studio User Written Transformation

Re: Assigning library in DI Studio User Written Transformation

Assigning library in DI Studio User Written Transformation

Re: proc sql: catching oracle exception

Re: Prevent %macro etls_createIndexes; in User Written Transformation ...

Prevent %macro etls_createIndexes; in User Written Transformation (DI ...

Re: LSF flow with several deployed jobs: how to run within a single SA...

Re: LSF flow with several deployed jobs: how to run within a single SA...

LSF flow with several deployed jobs: how to run within a single SAS se...

Re: proc sql: catching oracle exception

proc sql: catching oracle exception

Re: Assigning library in DI Studio User Written Transformation

Re: Assigning library in DI Studio User Written Transformation

Assigning library in DI Studio User Written Transformation

Working with Oracle sequence in SAS DI

Re: Adding a row in a table when SAS DI job starts

Adding a row in a table when SAS DI job starts

Re: Picking rows based on a newst date

Picking rows based on a newst date