SAS DI user written transformation

[Ramabc3]

Hi,

I am pretty new to SAS DI.

I am in process of migrating EG projects to DI most of it being user written code.

Would I be able to create new columns as part of code using user written transformation, rather then manually creating new columns?

Thanks

Ramaa

[LinusH · Accepted solution]

Unfortunately not (assuming you are meaning in metadata - which of course needs to go hand-in-hand with physical data, I wished this was the case though...

A bit of (unasked for) advice: don't just migrate EG code into user written DI jobs, it will give you headache the your to come. Use this migrating as an opportunity to structure your processes, and even enhance it.

(Editor's note: also see the advice from @DavidGhan for adding metadata details to your user-written transform, if that's the route you choose.)

[LaurieF]

I'm with @LinusH. Using the extract or join transformations you can create the new columns easily, which is the defined way of doing it. User-written transformations (which I'm a big fan of, by the way!) can do this but it requires fairly specific code to do it. In my opinion, user-written transformations should be generalised; I despair when I see, quite commonly, every single DI job in a suite has its own distinct transformation(s), because I know it's going to make the maintenance work extensive. If a dataset's own metadata and DI's metadata get out of sync, it'll all end in tears.

Similarly that SAS code is completely different from COBOL (I am that old), good DI and EG styles are quite different. 

[Ramabc3]

Thanks for the reply.

I used the extract trasformation created new columns and then the remaining code in the user written trasformation worked except for the retain statement

Well, I am actually trying to flatten a hierarchy file, which requires the initial values of the new column created to be retained. I am setting the initial value of the new columns as blank

Here is a piece of data step code that I used in the user written transofrmation:

Data Test; /*DEFINE NEW TABLE TO BE CREATED*/

                set Hier; /*DEFINE WHERE DATA SHOULD COME FROM*/

/*ALLOW NEW COLUMNS TO BE RETAINED FOR NEXT ROW AND PROVIDE INITIAL VALUE*/

               Retain LVL1 '';

                Retain LVL2 '';

                Retain LVL3 '';

/*POPULATE COLUMN VALUES AS APPROPRIATE*/

                 If Level = 1 then

                                                LVL1 = UNIT;

                If Level = 2 then

                                                LVL2 = UNIT;

                If Level < 2 then

                                                LVL2 = '';

                If Level = 3 then

                                                LVL3 = UNIT;

                If Level < 3 then

                                                LVL3 = '';

Run;

When I run it, it executes the if condition but not the retain statement. Ideally if LVL1 has a value that has to be retained for the subsequent rows until it finds the next one.

Please advise

Thanks

Ramaa Chavan

[DavidGhan]

I copied your code into a User Written code transformation in DI Studio, created a dataset like HIER for that transformation to read as an input dataset and I got it to work. I think maybe the issue is that the metadata for the input and output datasets (the ones on the SET statement and the DATA statement are not in sync with the physical datasets you are creating. Try the following:

1. Connect the dataset you want to read ( the one on your SET statement) to the User written transformation. You will need to register that table in the metadata to do so unless it was created by a prior transformation.

2. In your code write your SET statement this way:

        SET &_input1;    * the macro variable _input1 references the data source connected to the transformation;

3. In your code write your DATA statement like this:

        DATA &_output1;  * the macro variable _output1  references the target table that is connected to the transformation.

4. After you execute your transformation, save the job changes and then right-click on the output table connected to your user written transformation and select Update Table Metadata. Select Yes to proceed and either Yes or No about the next question related to viewing the details.

Now you can look at the data in your target table and see if it looks like what you want.

Everytime you change the code in your user written transformation in a way that affects the structure of the output table you will need to repeat step 4. It is also possible to add a small amout of post-code to the user written transformation to perform the Update Table Metadata operation to automate this.

Note: you may be able to avoid using &_input1 on the SET statement and therefore avoid registering and attaching  the HIER dataset to your User Written code transformation if the HIER dataset already exists and is available on your SAS server whenever you run the transformation. I would recommend going ahead and registering it in the metadata and connecting it to your transformation and using &_input1 to reference it in your job so that it is more integrated with the DI Studio interface.

Another note: I generally agree with others in this thread who recommended converting your code into transformations that do the equivalent if it is possible and if  you have time to do that. User Written code is best used for specialized code for which there are no equivalent transformations. ANd the techniques I have described here there are great ways to incorporate user written code into DI Studio jobs as well when you need to do so.

[Ramabc3]

Thanks David, will try and let you know how it goes

[Ramabc3]

Hi David,

I contected the input and output tables to the transformation and changed the code Set &_input1; and Data &_output1;

I get this below error message :

12064     Data &_output1; /*DEFINE NEW TABLE TO BE CREATED*/

               _

               22

               200

ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, /, ;, _DATA_, _LAST_, _NULL_.

ERROR 200-322: The symbol is not recognized and will be ignored.

12065                   set &_input1; /*DEFINE WHERE DATA SHOULD COME FROM*/

               _

               22

               200

WARNING: Apparent symbolic reference _INPUT1 not resolved.

ERROR: File WORK._INPUT1.DATA does not exist.

ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, ;, CUROBS, END, INDSNAME, KEY, KEYRESET, KEYS,

             NOBS, OPEN, POINT, _DATA_, _LAST_, _NULL_.

ERROR 200-322: The symbol is not recognized and will be ignored.

Not sure if I need to define these variables "_input1" and "_output1".

Thanks

Ramaa

[Ramabc3]

Hi David,

The entire code worked fine after registering the table and the metadata.

Although the Set &_input1 and Data &_output1 as mentioned earlier did not work, hence I had to leve it the way it was.

Thanks

Ramaa

[DavidGhan]

In the Code tab within the Properties of the User Written transformation there is a choice called "Code generation mode". Make sure that "User written body" is selected there rather than "All User Written". That will cause &_input1 and &_output1 to be defined for you (as long as you attach the source table as a source table to the User written transformation and you have a target table as the output of the transformation. By default when you add the transformation to the job it will have a work table as a target table).

I am not sure why you are getting the first error on line 12064. Perhaps if you send me the entire log so I can see what preceeds the DATA statement. The error may be caused by something you added before that DATA statement.Can you send me the complete log?