SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Data Extraction from source

Reply
New Contributor
Posts: 2

Data Extraction from source

[ Edited ]

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

Super User
Posts: 7,777

Re: Data Extraction from source

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 5,427

Re: Data Extraction from source

Are you using DI Studio?
Data never sleeps
New Contributor
Posts: 2

Re: Data Extraction from source

Hi,

Yes. Im using DI for this.

Thanks!
Raj

##- Please type your reply above this line. Simple formatting, no
attachments. -##
Super User
Posts: 5,427

Re: Data Extraction from source

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.
Data never sleeps
Ask a Question
Discussion stats
  • 4 replies
  • 362 views
  • 0 likes
  • 3 in conversation