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

DI Studio - Creating a file on the fly

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 11
Accepted Solution

DI Studio - Creating a file on the fly

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!


Accepted Solutions
Solution
‎03-16-2015 08:37 PM
SAS Employee
Posts: 340

Re: DI Studio - Creating a file on the fly

With SAS code you would write something like this:

%let timestamp=%sysfunc(date(),yymmddn8.);

data _null_;

     set myLib.myTable;

     file "C:\temp\myFileName_&timestamp";

     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_&timestamp

     - 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

View solution in original post


All Replies
Solution
‎03-16-2015 08:37 PM
SAS Employee
Posts: 340

Re: DI Studio - Creating a file on the fly

With SAS code you would write something like this:

%let timestamp=%sysfunc(date(),yymmddn8.);

data _null_;

     set myLib.myTable;

     file "C:\temp\myFileName_&timestamp";

     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_&timestamp

     - 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

Occasional Contributor
Posts: 11

Re: DI Studio - Creating a file on the fly

Thank You!!!

Your instructions were awesome ... that's exactly what i needed.

Respected Advisor
Posts: 3,889

Re: DI Studio - Creating a file on the fly

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.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 691 views
  • 0 likes
  • 3 in conversation