Filters in Table Loader transformation

[David_Billa]

Is there a way to apply a filter (where clause) in Table loader transformation?

[Patrick]

You can subset data in the Table Loader Properties under tab Table Options, Advanced.

What you get there will depend on the type of your source table (SAS or a database) but there will be a place where you can enter a condition to subset the data.

[David_Billa]

My source table is SQL database table now. However I'm not seeing the place where I can write a condition to subset the data. I could see that can write a WHERE clause under advanced Options if my source table is SQL Server.

[Patrick]

---

@David_Billa wrote:

My source table is SQL database table now. However I'm not seeing the place where I can write a condition to subset the data. I could see that can write a WHERE clause under advanced Options if my source table is SQL Server.

---

I don't have an environment with SQL Server so can't tell you how the UI looks like for this situation.

Here how it looks like for Hadoop.

If you really can't find a spot to define a where condition then another option is to use a SQL Join transformation. 

Just remove the join condition so it only uses a single table as input, define the where condition and the output as a view. Then feed the view as source to the table loader. Doing it this way should still generate code that fully executes in-database as long as you don't use SAS functions in the where clause which SAS can't put to the database for execution.

[David_Billa]

I'm dealing with SAS dataset as a source. So can I understand that I can write a where clause in advanced options under the tab table options?

[Patrick]

---

@David_Billa wrote:
I'm dealing with SAS dataset as a source. So can I understand that I can write a where clause in advanced options under the tab table options?

---

Yes, that's correct.

There is an entry field for a WHERE condition there. DIS will then use this condition to subset the source table.

proc append base=target data=source(where=(<condition as defined>));
run;

[RLigtenberg]

MSSQL Source Table to Table LoaderTable Loader to MSSQL Target TableGenerated code

Generated code

[David_Billa]

Should I do anything in the table loader transformation before I get the
generated code?

[RLigtenberg]

Type the code in the text fields shown in the first two screen shots.

where=(x=1)

where=(y=2)

[David_Billa]

May I know whats the load technique in your example? I've the load technique as below and it is not subsetting the data from target SQL table. Calling @Patrick  also for the help.

I've given the filter condition under Table Options in General  and Advanced but it is not working. If I see the code under CODE tab it is like 'insert into (where=(<condition>)) select *....' and it is simply loading all the data from source instead of filtering the data based on condition.

[Patrick]

@David_Billa 

I'm observing the similar code generation when using the "insert (SQL)" for new rows: The where condition doesn't get generated.

If I use "Append" then the where clause gets generated as a table option - I can't test it but I believe that's not valid syntax for database tables and the access engine won't push such syntax to the database for execution.

For what you've posted: insert into (where=(<condition>)) select *....

Don't use such a table option on the target table. You need to sub-set the source table.

If going for SQL INSERT code then why not use SQL transformation "Insert Rows". There things work and you can sub-set the source table as you need to. 

[David_Billa]

I believe "append" will work as it filter the record from the source table.
In my case source and target table is SQL table.

How do you say "append" might not work?

[Patrick]

---

@David_Billa wrote:
I believe "append" will work as it filter the record from the source table.
In my case source and target table is SQL table.

How do you say "append" might not work?

---

Well, just try it.

Using the table loader with Append DIS will generate code as below:

%let etls_tableOptions= %nrquote(where=(<condition));

proc append base=target data=source(&etls_tableOptions);
run;

As said I can't test it but I believe using a where clause as a table option only works for SAS tables but not for data base tables. 

If source and target are both SQL tables then I'd be using the SQL insert transformation in any case. I'd also select "yes" for explicit pass-through as documented here.

"...open the properties window for the transformation and click the Options tab. Specify Yes for the Database pass-through option"

[David_Billa]

Append seem to be working with SQL tables as well. If you have DB tables
you can give a try.

[Patrick]

If you add the following options before the append/insert code then SAS will write to the log what it sends to the database for execution.

options sastrace=',,,d' sastraceloc=saslog nostsuffix;

Make sure that the process executes fully in-database and that SAS doesn't pull anything to the SAS side for sub-setting the rows.

Again: I would use the SQL INSERT transformation as there you can set up things "directly" and closer to the actual code to be executed.