A User Written transformation in DI Studio uses the following code: proc sql;   create table work.oppspartkap as   select polisenr, periode_yyyymm, oppspartkapital2 from &_input; quit; data &_output;   set work.oppspartkap;   by polisenr notsorted;    if first.polisenr; run; After running it, and applying a filter so that it only displays rows with polisenr 1, the work table looks like this: I need to edit the user written code so that identical rows, such as the three highlighted ones, only appear once. Not sure whether I should use "distinct" somewhere, "group by" somewhere, or something else. Kindly advise me, thanks. ... View more

Sometimes, when opening a job I've made alterations to in the past, all the objects (transformations and tables) are bundled on top of each other in the upper-left corner, and all connections are gone. Have any of you encountered this problem before, and do you know what might be causing it? Thanks for your attention. ... View more

I have tested that the following code successfully copies the physical table "Employee" from Lib1 to Lib2. However, I need to edit the code so that it copies all the physical tables in that location, but not the folders. How can I accomplish this? proc copy in = Lib1 out= Lib2 index=yes constraint=yes;   select Employee; quit; Thanks for your time. ... View more

I've created a loop-job which runs the inner job once per month, from the previous month and back until october 2011. The job is parameterized so that -1 indicates one month back, -2 indicates two months back, etc. The control table currently contains the variable "month_back" with values from 1- to -13, so that the inner job is run from oct2012 to oct 2011. My problem is that the values in the control table are static, so each month I have to manually add another number so that it goes all the way back to oct2011. What I need is user written code that creates a table where month_back is automatically filled out with all the proper values according to the current month. If I run it today it should get values from -1 to -13, if I run it next month it should get values from -1 to -14, etc. I'd appreciate any advice on how to accomplish this. Basic start: data work.months_back;      %let startdate = %sysfunc(intnx(month, %sysfunc(date()), -1, E), date9.); *appears as 31OCT2012 if run in November;      month_back = -1 *one month back;      *must get values for all months back until october 2011; run; data &_output;      set months_back; run; ... View more

I have a datetime macrovariable called &extract_dttm, which has the value 31OCT12:23:59:59. Based on this, I need to create two additional macrovariables: &period_dt, which should get the value 31OCT2012. &period_yyyymm, which should get the value 201210. Can someone please advise me on how to accomplish this in the precode of an Extract transformation? Thank you. ... View more

I'm using the following precode in an Extract transformation: %let extract_dttm = intnx('DTMONTH',DATETIME(),&back.,'E'); %put &extract_dttm; The purpose of the Extract, if run today, is to extract all rows with extract_dttm between Valid_From_DTTM and Valid_From_DTTM The &back parameter is -1, to designate the previous month. When I run the Extract, %put &extract_dttm prints the text "intnx('DTMONTH',DATETIME(),-1,'E')" to the log. How do I turn this into a value? I need to be able to use it in this WHERE-statement: &extract_dttm. BETWEEN Valid_From_DTTM AND Valid_From_DTTM Thanks for your time. ... View more

I recently removed an "SCL Type 2 Loader" from a DI Studio job, and created a new surrogate PK for the output table. The job ran fine. However, now that I've transferred the job to a new environment, I get the following warning for each of the four variables that were removed along with the SCL Type 2 Loader (policy_sk, valdidfrom_dttm, validto_dttm, and current.): "WARNING: Variable <varname> was not found on BASE file. The variable will not be added to the BASE file." I'd greatly appreciate advice on how to fix this. Thanks. ... View more

In the precode of my Extract transformation, I need to establish a macro variable that designates Tuesday last week, relative to the date the job is run. If I run it today, it should appear like this: 09OCT12:00:00:00. I'm almost there, but need help with a detail. This is my current code: %let last_tuesday = %sysfunc(intnx(week, %sysfunc(today()),-1,b), date9.):00:00:00; Logically enough, it sets the date to 07OCT12:00:00:00. Next I need to add two days to it, to make it Tuesday(3) instead of Sunday(1). However, no matter where I try to insert "+2", it seems to fail. Can someone please advise me how I can edit the above code to make it add two days? P.S. I've tried using week1.3 and week.3 in the above code, but those only select the Tuesday of the first week of the month no matter when the job is run (02OCT2012:00:00:00 if I run it now). Thanks. ... View more

I've been asked to remove the "SCD Type 2 Loader" transformation from a job in DI Studio. The reason is that it is superfluous, because everything can be handled as type 1. Removing the SCD Type 2 Loader transformation seems straight-forward, however it appears that it's also responsible for generating the surrogate PK used in the output table. What is the best alternative for generating a surrogate PK once the SCD Type 2 Loader transformation has been removed? And also, is there anything else I should bear in mind when removing a SCD Type 2 Loader transformation from a job? Thanks for your attention. ... View more