topic Re: Use date filter in Extract - DI Studio in SAS Data Management

Use date filter in Extract - DI Studio

DavidCaliman — Wed, 18 Dec 2013 16:18:00 GMT

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.

Re: Use date filter in Extract - DI Studio

DavidCaliman — Wed, 18 Dec 2013 16:20:13 GMT

Table LOAD_DATE has only 1 record.

Re: Use date filter in Extract - DI Studio

LinusH — Wed, 18 Dec 2013 16:20:54 GMT

Use the SQL Join transformation.

Re: Use date filter in Extract - DI Studio

DavidCaliman — Wed, 18 Dec 2013 16:42:10 GMT

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

Re: Use date filter in Extract - DI Studio

LinusH — Thu, 19 Dec 2013 08:16:28 GMT

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

Re: Use date filter in Extract - DI Studio

Michael_SAS — Thu, 19 Dec 2013 19:17:21 GMT

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

Re: Use date filter in Extract - DI Studio

Patrick — Thu, 19 Dec 2013 20:09:40 GMT

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;