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.
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
Table LOAD_DATE has only 1 record.
Use the SQL Join transformation.
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
I think a join is simple, I don't think there is a simpler way if you want to stick with standard transformations.
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
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;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.