03-10-2016 03:06 PM - edited 03-10-2016 03:09 PM
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
03-11-2016 03:29 AM
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.
proc sql; select min(date) into: mindate from transaction_table; quit;
so you can use &mindate later in the same job.
03-12-2016 04:46 AM