topic Data Extraction from source in SAS Data Management

Data Extraction from source

Rajiv468 — Thu, 10 Mar 2016 20:09:56 GMT

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

Re: Data Extraction from source

Kurt_Bremser — Fri, 11 Mar 2016 08:29:11 GMT

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.

Re: Data Extraction from source

LinusH — Fri, 11 Mar 2016 16:53:06 GMT

Are you using DI Studio?

Re: Data Extraction from source

Rajiv468 — Sat, 12 Mar 2016 04:11:26 GMT

Hi,

Yes. Im using DI for this.

Thanks!
Raj

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

Re: Data Extraction from source

LinusH — Sat, 12 Mar 2016 09:46:24 GMT

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.