topic Re: DI Studio - Creating a file on the fly in SAS Data Management

DI Studio - Creating a file on the fly

goitzy — Mon, 16 Mar 2015 22:23:57 GMT

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!

Re: DI Studio - Creating a file on the fly

gergely_batho — Tue, 17 Mar 2015 00:37:14 GMT

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

Re: DI Studio - Creating a file on the fly

Patrick — Tue, 17 Mar 2015 07:40:24 GMT

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.

Re: DI Studio - Creating a file on the fly

goitzy — Wed, 18 Mar 2015 15:44:40 GMT

Thank You!!!

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