topic Filters in Table Loader transformation in SAS Data Management

Filters in Table Loader transformation

David_Billa — Fri, 08 Nov 2019 16:44:19 GMT

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

Re: Filters in Table Loader transformation

Patrick — Fri, 08 Nov 2019 23:32:32 GMT

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.

Re: Filters in Table Loader transformation

David_Billa — Sat, 09 Nov 2019 12:41:36 GMT

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?

Re: Filters in Table Loader transformation

Patrick — Sun, 10 Nov 2019 23:22:48 GMT

@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;

Re: Filters in Table Loader transformation

David_Billa — Thu, 14 Nov 2019 07:36:17 GMT

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.

Re: Filters in Table Loader transformation

Patrick — Thu, 14 Nov 2019 12:50:28 GMT

@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.

Re: Filters in Table Loader transformation

RLigtenberg — Thu, 14 Nov 2019 16:23:19 GMT

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

Generated code

Re: Filters in Table Loader transformation

David_Billa — Thu, 14 Nov 2019 16:31:23 GMT

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

Re: Filters in Table Loader transformation

RLigtenberg — Thu, 14 Nov 2019 17:50:39 GMT

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

where=(x=1)

where=(y=2)

Re: Filters in Table Loader transformation

David_Billa — Tue, 04 Feb 2020 07:37:28 GMT

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.

Re: Filters in Table Loader transformation

Patrick — Tue, 04 Feb 2020 23:48:27 GMT

@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.

Re: Filters in Table Loader transformation

David_Billa — Wed, 05 Feb 2020 02:14:12 GMT

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?

Re: Filters in Table Loader transformation

Patrick — Wed, 05 Feb 2020 02:44:28 GMT

@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"

Re: Filters in Table Loader transformation

David_Billa — Wed, 05 Feb 2020 02:44:12 GMT

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

Re: Filters in Table Loader transformation

Patrick — Wed, 05 Feb 2020 02:48:53 GMT

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.

Re: Filters in Table Loader transformation

ErikLund_Jensen — Wed, 05 Feb 2020 15:32:48 GMT

Hi @David_Billa

The answers so far shows that a condition can be put into a table loader, but it is a bad idea, because it obscures what's going on and makes maintenance and troubleshooting a hell in the long run. I know from long experience...

When the job is opened, there is nothing on the canvas to tell that the loader has been tampered with down in the third subpane, so everybody would exepect it to be 1:1, The same with mapping and recoding. Don't drop variables or add expressions in the loader.

it takes less than a minute to insert an exctact (with output as a view) before the loader and do any subsetting and recoding there. Creating a view doesn't use any ressources, anyone opening the job can see that something is going on, and it is much easier to figure out what happens, because it is found in the where- and mapping-panes, not hidden in a subpane most people doesn't even know exists..

Re: Filters in Table Loader transformation

Patrick — Wed, 05 Feb 2020 23:20:15 GMT

@ErikLund_Jensen

Just adding some opinion:

- Where clause in table loader: Whenever you do something "special" in a transformation add some comment so it does become visible on the job canvas.

- Using views for in-database processing means that you must be allowed to create such views in the database and you also need to drop the view after the load. That's another reason why I'd be using a SQL Insert transformation instead of the Table Loader.