Data Extraction from source

[Rajiv468]

Hi, Im doing ETL for new system/DM. My Target can only have 2 years data anytime though source can have much more than that. I have 2 transaction files(Assume 2 Business Unit's or Product's) and other tables for dimensions and few reference tables etc.. Now i have to pickup min date from both transaction files(may be different for each file) and extract data from other tables/files only if it is >= min(date). I have to pass this min date as variable for extraction to other jobs or the all the staging jobs should know that data can contain only >= min(date) for that particular business Unit

from min(date)  to next 2 years or 24 months data to be extracted this way. 

. Can somebody help me with approach and ways to achieve this? Thank You! Raj

[Kurt_Bremser]

You can pass such reference constants to your SAS batch jobs via commandline (using the -sysparm commandline option), through environment variables (which can be questioned with the %sysget macro function), or by reading from an external file

So, if you have separate jobs to be run, first run one that determines the min date and writes it to a file. Then you can read that file in subsequent jobs and retrieve the date.

If you do it in one job, it is much easier.

Do

proc sql;
select min(date) into: mindate from transaction_table;
quit;

so you can use &mindate later in the same job.

[LinusH]

Are you using DI Studio?

[Rajiv468]

Hi,

Yes. Im using DI for this.

Thanks!
Raj

##- Please type your reply above this line. Simple formatting, no
attachments. -##

[LinusH]

Not sure ix I follow the overall design, bug to answer the direct question: extract the nib date(s) and store in a permanent table. Then have other jobs either create a macro variable from it, or join, depending on how you wish to apply you logic.
Macro variable can be created in the autoexec, pre step or in a user written transformation.