how to Load Row Number in SAS DI

[Helannivas]

I need to load  row number in sas DI.

For Eg: In Source

Name      Emp_ID

Raj          123

Kumar     456

  .              .

  .              .

Ravi         789

My target must be like this.

Name         Emp_ID    Row_No.

Raj             123           1

Kumar         456          2

   .                .              .

   .                .              .

  Ravi           789           35

How to do this? Pls help me on this.

[jayachandra84_gmail_com]

Hi Helannivas ,

IF u want  row number in sas DI

Use Surrogate Key transformation in this transformation properties create new column(Row _NO) in target table  and mapping to source column emp_ID to  target table column Row_NO)..

[Helannivas]

Thanks for your reply...

We are not using any primary keys constraint in my target table.

We are loading from flat file to table.I tried using surrogate key generator.

The surrogate key columns  is not getting updated.

How to proceed further?

[Gaurang_sas]

I have never done it but you can use Row_no = _n_;

[deleted_user]

Hi,

1. Use a user written transformation and table loader to solve the problem.

use the below code in the user written transformation;

data _output_;

set <your source>;

Row_NUM=_n_;

run;

2. Connect out put of this transformation to Table loader and table loader to your output data set.

Please let me know if you have any concerns.

Thanks,

Chakra..

[Helannivas]

Thanks for your reply...

I have used monotonic() function for row number and its working fine.

I have one more question.

My source files has some null values.For Eg.

Name  Emp_Id

Raj      123

null      456

I need to load as empty string in target.I have used coalesce function (coalescec(Name,' ')) .

But still i am not getting the o/p.

Pls help me on this.

[deleted_user]

Try this  coalescec('Name',' ')

[Helannivas]

When I used coalescec('Name',' ' ),the Name column has the values as name in both the rows.(like this)

Name   Emp_Id

Name     123

Name     456