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 ... View more

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     ... View more

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

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     ... View more