SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Use date filter in Extract - DI Studio

Accepted Solution Solved
Reply
Contributor
Posts: 58
Accepted Solution

Use date filter in Extract - DI Studio

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.


Accepted Solutions
Solution
‎12-19-2013 02:17 PM
SAS Employee
Posts: 13

Re: Use date filter in Extract - DI Studio

Posted in reply to DavidCaliman

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

View solution in original post


All Replies
Contributor
Posts: 58

Re: Use date filter in Extract - DI Studio

Posted in reply to DavidCaliman

Table LOAD_DATE has only 1 record.

Super User
Posts: 5,437

Re: Use date filter in Extract - DI Studio

Posted in reply to DavidCaliman

Use the SQL Join transformation.

Data never sleeps
Contributor
Posts: 58

Re: Use date filter in Extract - DI Studio

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

Super User
Posts: 5,437

Re: Use date filter in Extract - DI Studio

Posted in reply to DavidCaliman

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

Data never sleeps
Solution
‎12-19-2013 02:17 PM
SAS Employee
Posts: 13

Re: Use date filter in Extract - DI Studio

Posted in reply to DavidCaliman

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

Respected Advisor
Posts: 4,173

Re: Use date filter in Extract - DI Studio

Posted in reply to Michael_SAS

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;

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 919 views
  • 3 likes
  • 4 in conversation