Writing output to a external text file from SAS DI Studio

[dphilip01]

I have to create some specialized  reporting. Traditionally, when using SAS EG or SAS Desktop, this would have been done

using a "filename  statement" . For example  "Filename rptout ' C:\myfolder\myreport.txt";

data _null_;

   file rptout;

   set rptdata;

put @15 'Some Report Title';/

put @01 @ detail_ln;/

run;

filename rptout clear;

SAS DI is saying that one must use the "File Writer Transformation" which seems overly complex to me.  What is the best way to

write to a text file in SAS DI"  Is using the "File Writer Transformation" the bes way to go?  Please advise and Thank you very much

in advance.

[LinusH]

The file format and implicitly generating SAS code, is specified in the External File object.

That said, the File Writer transforamtion is unfortenately not a transformation you want to refer to when bragging about DI Studio.

If that transformation is not sufficient for your needs, you can always generate the file using User Written, or even a User Written Transformation.

Not sure how you define a "report", but usually DI Studio is not used as a report genration tool, it's targeted for ETL purposes.

[dphilip01]

Hi Linus H,



Thank you very much for taking the time to address my question. I understand what you're saying about SAS DI being a tool for ETL.

This has been an adjustment coming from SAS EG and SAS desktop. So there doesn't seem to be any easy way to write to a plain

asci text file using " put statements". Looks like all the input and output has to be in a table or delimited file.



I did send this question to SAS Technical support as well but I thank you for your efforts.



Darryl

[ErikLund_Jensen · Accepted solution]

Hi @dphilip01 

It is not difficult to do it in DI Studio, as long as you forget about the file writer and utilize the not-so-well-known feature that a file can be connected to a user written code transformation as output, if the default output work table is deleted.

DI Studio works with metadata objects, some of them representing physical objects, like libraries, permanent tables, deployed jobs and external files, while other only exist as metadata used in generating job code etc.

Any sort of SAS code can be written and executed in DI Studio, and the code can contain references to physical things in libname and filename statements etc, but they are "invisible". They do not appear in the inventory pane, and they cannot be traced, i.e. they do appear in an impact analysis. So in the long run it is a good idea to register these objects in metadata. 

In your case, I would recommend the following:

1. Create the output file physically as an empty file in a folder that is visible from the DI server context.

2. Register the file in DI Studio. It is a simple task:

• Right-click on the DI Studio folder and select New -> External File -> delimited file.
• Give it a name and a physical name with full path. 
• Just click next - next and ignore the warning about "no columns".  

3. Pull a User Written transformation (found under "Data") into your job and delete the work table on the right side of the transformation.

4. Pull the input table into your job and connect it to the transformation as input.

5. Pull the file into your job and connect it to the transformation as output.

Then you have all metadata registrations in place. Open the transformation properties, select the Code pane and write your code as shown in this example. Note that DI Studio supplies macro variables for _INPUT and _OUTPUT which can be used in the code, so the code is unaffected by changes in table- and file names.

Cut from the code pane. The macro assignments are supplied by the transformation, the data step is the userwritten code:

Please ignore the two small blue circles, they came without my knowing why...