BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
goitzy
Calcite | Level 5

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!

1 ACCEPTED SOLUTION

Accepted Solutions
gergely_batho
SAS Employee

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

3 REPLIES 3
gergely_batho
SAS Employee

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

goitzy
Calcite | Level 5

Thank You!!!

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

Patrick
Opal | Level 21

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.

sas-innovate-2024.png

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.

 

Register now!

How to connect to databases in SAS Viya

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.

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