Use date filter in Extract - DI Studio

[DavidCaliman]

Hi!

I need inside an extract in DI Studio, using a date filter, accessing another table.

How do I do that?

Example:

SOURCE -> EXTRACT

In Extract, use the filter:

SOURCE.DATE> LOAD_DATE.DATE

LOAD_DATE is as SAS table.

[DavidCaliman]

Table LOAD_DATE has only 1 record.

[LinusH]

Use the SQL Join transformation.

[DavidCaliman]

Is there any simpler way to do this without using join?

For parameter passing or directly accessing the table, using the filter below?

In Extract, use the filter:

SOURCE.DATE> LIBNAME.LOAD_DATE.DATE

[LinusH]

I think a join is simple, I don't think there is a simpler way if you want to stick with standard transformations.

[Michael_SAS · Accepted solution]

an alternative to a join, would be to use a macro variable. I use the pre-code in the transform to set the macro  variable something like

PROC SQL NOPRINT;

SELECT A.DATE into :LOAD_DATE FROM TABLE;

QUIT;

then reference the macro variable in your FIlter transform

SOURCE_DATE > &LOAD_DATE

if source_date is a "date" data type you'll want to cast your macro variable to a date

give that a try

mike

[Patrick]

If using Michael@SAS approach and your SOURCE table is in a data base and not SAS then make sure that you pass the macro variable in a form which enables the SAS Access engine to detect that this is a date value and needs conversion into the DB equivalent.

One way how this would work is to populate the macro variable with a string like 31DEC2013 which you then use in your SQL where clause like:

.... where source.date="&load_date"d

PROC SQL NOPRINT;

  SELECT put(max(DATE),date9.) into :LOAD_DATE FROM control_table;

QUIT;