I need to create a process that creates a file daily and outputs it to a file system to be consumed by another process.
The file name must be in the format filename_timestamp and the data source is table in some sql server database.
Being new to DI Studio my assumption that it would be a customer stored process that would;
1. Create the file
2. select from the table
3. write to file
I have figured out how to write to a file without issue but i need to pre define and create the file manually before i run the job.
Any help would be appreciated!
With SAS code you would write something like this:
%let timestamp=%sysfunc(date(),yymmddn8.);
data _null_;
set myLib.myTable;
file "C:\temp\myFileName_×tamp";
put Col1 Col2 Col3;
run;
Within DI Studio first you create a File object that has a parameter, then you create a job object that has a parameter:
1. Define the metadata for the file:
- New ->External File -> Delimited (or Fixed)
- Specify the file Metadata Name, for example: myFileName
- Specify the file physical location and name, for example: C:\temp\myFileName_×tamp
- Under Advanced - File Name Quoting specify "Double quotes around the file"
- Under Parameters specify a New Prompt: the name is important. For example: timestamp , maybe you can specify a default value (Observe, that at this point, the icon of the file object changes. Now it has an & sign. )
The rest is as usual
The file object that you created does not reference a single physical file anymore. It represents a group of files: all files, that start with C:\temp\myFileName_ and end with a timestamp.
2. Create a job
- The diagram should contain the data source, a File Writer transformation, the File object.
- Look at the Properties of the job and select Parameters. Here, you could define the same parameter, but it is better to import the previous definition: Click on Import Parameters (Observe, that the jobs icon has an & now.)
- Click on the Precode and Postcoe and type specify the Precode, that creates the timestamp parameter (macro variable). For example: %let timestamp=%sysfunc(date(),yymmddn8.);
3. Run the job
With SAS code you would write something like this:
%let timestamp=%sysfunc(date(),yymmddn8.);
data _null_;
set myLib.myTable;
file "C:\temp\myFileName_×tamp";
put Col1 Col2 Col3;
run;
Within DI Studio first you create a File object that has a parameter, then you create a job object that has a parameter:
1. Define the metadata for the file:
- New ->External File -> Delimited (or Fixed)
- Specify the file Metadata Name, for example: myFileName
- Specify the file physical location and name, for example: C:\temp\myFileName_×tamp
- Under Advanced - File Name Quoting specify "Double quotes around the file"
- Under Parameters specify a New Prompt: the name is important. For example: timestamp , maybe you can specify a default value (Observe, that at this point, the icon of the file object changes. Now it has an & sign. )
The rest is as usual
The file object that you created does not reference a single physical file anymore. It represents a group of files: all files, that start with C:\temp\myFileName_ and end with a timestamp.
2. Create a job
- The diagram should contain the data source, a File Writer transformation, the File object.
- Look at the Properties of the job and select Parameters. Here, you could define the same parameter, but it is better to import the previous definition: Click on Import Parameters (Observe, that the jobs icon has an & now.)
- Click on the Precode and Postcoe and type specify the Precode, that creates the timestamp parameter (macro variable). For example: %let timestamp=%sysfunc(date(),yymmddn8.);
3. Run the job
Thank You!!!
Your instructions were awesome ... that's exactly what i needed.
With DI Studio you need to create SAS Metadata.
- Table metadata for your source from a data base
- External file metadata for your extract file
Use the File Writer transformation to create the output.
Define the External File metadata with a filename containing a macro variable (or macro level function) as part of the file name (this allows you to have a single metadata object writing to different physical files).
You don't need to pre-create an empty file. SAS will do this for you.
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.