Hi, Iam hoping for some help, within DI studio I have various External file objects set up, I would like to be able to query all these objects and pull back the column headers.  I cannot seem to find any documentation about how to access the External file objects, does anyone have any idea on how this can be done? Thanks Tom ... View more

Hi, I am trying to utilise the type 1 table loader so I can update a table with only changed dimensions.  The table has 70k rows and 5 variables.  I require 3 varibale s as my lookup to check for any change and then I have one target column that is changed.  This is the first time I have  used the a SCD and I am finding it can take up to 15 miutes to run with potentially only 3k records changing.  Firstly is this normal? Secondly how can I optimise this time?  I have experimented with different hash lookup sizes and no noticeable improvement is made. Any ideas? Thanks ... View more

Hi, I currently am creating a table using proc SQL. The table has 50 columns representing days 1 - 50. All the columns use the calcualted function to get the following logic: Result of column 2 is based on calculation in column 1 Result of column 3 is based on calculation in column 2 Result of column 4 is based on calculation in column 3 Result of column 5 is based on calculation in column 4 etc Result of column 50 is based on calculation in column 49 This is incredibly slow to run and no doubt in efficient.  Is there a better way of doing this? Thanks Tom ... View more

Hi, A few months ago I set up a straightforward process that took two permanent tables (e.g. registered as metadata and stored in library) and used a SQL join transformation.  I then right clicked on the table icon and selected register to create another permanent table with the result of the join.  I would now like to come back and modify the process to put another step in after the join and before loading to premanent dataset. however now when I delete the link and try add in the step (splitter transformation) I am told that "splitter requires a physical table or work table"  Is there a way without rewriting the join transformation that I can modify the existing. Thanks Tom ... View more

Hi I am using a file writer transformation in DI studio 4.4 to write a dataset to a text file.  I am faced with two problems, firstly every fiels is enclosed in quotation marks.  I believe this is because within some of the field I have non alphanumeric characters like "-".  The second issue I have is that there is a field with numeric characters.  They look fine in the dataset but when exported they are exported as 2.99e6 (or something similar) this ends up being 290000.  Most importantly how can I maintain the number in the field? secondly how can I export without quotation marks. I have managed to export without quotation marks by editing the code to remove all references to "quote" (see below) although I would rather use a standard transformation if possibkle than modify one, VAR 5 is the numeric field.  I have highlighted the fields I remove/ modify to get output without quotes. %let SYSLAST = work.W4VOE3O; data _null_;    set &SYSLAST;       attrib VAR1 length = $3;      attrib VAR2 length = $3;      attrib VAR3 length = $3;      attrib VAR5 length = 8;             quote='"';       file "$DISC_FTP/info_today_date1._&tf_hour.00.txt" LRECL=32760 dlm='|';                put             quote +(-1)VAR1  +(-1) quote                quote +(-1)VAR2  +(-1) quote                quote +(-1)VAR3  +(-1) quote                quote +(-1)VAR4  +(-1) quote                quote +(-1)VAR5  +(-1) quote                         ; run; Thanks ... View more

Hi, The below code has its inputs assigned by a proc SQL into statement. So for each line of a statement I cycle through a "control table" assign the variables and run %macro builder. each iteration of the macro builder appends the data to the bottom of one of two permanent tables.  The table is dictated by a concatenation of &horizon + &Level.  In reality &horizon is always equal to TEM and &level is equal to either tab1 or tab2.  So end result is perm.TEMtab1 or perm.TEMtab2.  The macro runs and produces the desired results although I get a warning around &level as it is sometimes length 5 and sometimes len 20.  More specifically when &level is equal to tab1 it is equal to 5 and when tab2 length is 20. Is there a way I can add to the select statement a condition which sets the length as either 5 or 20 dependant upon the value assigned to &level.  My understanding is there should be however I have nt managed to get it working. Thanks for reading.   %macro builder(horizon=,level=,output_name=,output_row=,date_var=,calculation=,data_source=,where=); proc sql; create table &horizon._&level as select unit, compress(&domain) as domain length =2, &level length =20, "&output_name" as output_name length=50, &output_row as output_row, %do i=1 %to 10 sum(case when datdif(date()-&dateoffset, &date_var, 'act/act') = &i then &calculation else 0 end) as d&i %if &i < 10 %then %do;    ,   %end; %end; from &data_source %unquote(&where) group by business_unit, domain, &level; quit; proc append base=perm.&horizon._&level data=TEM_&level force; %mend builder; ... View more