DI Studio - File Writer Transformation pipe delimited Variables

[gsmith]

I need to write to a pipe delimited file using the File Writer transformation in DI Studio (4.902)

There are two requirements in the spec that are giving me problems.

1) The header row (Data set variables) needs to have a leading and ending pipe.

2) The observations need to have a leading and ending pipe. I have solved this inelegantly by using the CAT function on the first and last variable in the data set to add the pipe directly to the values in the mapping tab. ex.  (CAT('|','<Value>')) 

I'd prefer a better solution.

The text in the file needs to look like this:

|Name|Age|City|       <----  Variable

|Sam|42|Denver|      <----  Observations

|Jill|37|Chicago|

I have no idea how to solve number one.  Can't just cheat and add a pipe to the variable name since a pipe is an invalid character.

[LinusH]

I have never come across such a file requirement so my first option would be to challenge it.
Then I don't think that there are any elegant solution to this. One could be to have non SAS script prepping the file with the leading and ending pipes.
If this is requirement that is repeating you could use Use Written File and have your file creation logic in a macro.

[gsmith]

I met with the vendor today and the file spec requirement is correct. I agree that it is unusual. We are giving them a carriage return on each line and that should be sufficient, but I guess not.

[gsmith · Accepted solution]

I have solved it!

Like most problems, the solution was right in front of my face.

Using the File Writer transformation, I generated the code as before. Standard pipe delimited with the values of the first and last variable in the "Mappings" tab concatenated with leading and trailing pipes.

Then, to get the same on the first row of variable names, once the code was created by the transformation, I went to the code tab and changed the drop down to "All user written code" and simply added the pipes at the beginning and the end of the put statement.

if ( _n_ = 1 ) then
         do;
            put
               "|<var1>|<var2>|<var3>|<var4>|<var5>|<var6>|<var7>)|";
               end;

[LinusH]

Great,
But be aware of if you need to change anything in the future, you need to either
1. revert back from all user written, do the changes in metadata (like adding a column), change to all user written, and repeat the pipe syntax changes.
2. Do your changes total manually in code and in metadata in paralell, risking (even more) that code and metadata will be out of sync.

[gsmith]

Thanks for the ideas.

One thing I did do was attach a document to the transformation detailing what I did and why so the next person through has context and understanding of the code changes.

I am a huge proponent of in-code detailed documentation.