BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
DavidCaliman
Calcite | Level 5

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Michael_SAS
SAS Employee

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

6 REPLIES 6
DavidCaliman
Calcite | Level 5

Table LOAD_DATE has only 1 record.

LinusH
Tourmaline | Level 20

Use the SQL Join transformation.

Data never sleeps
DavidCaliman
Calcite | Level 5

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
Tourmaline | Level 20

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
Michael_SAS
SAS Employee

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
Opal | Level 21

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

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