topic Re: how to Load Row Number in SAS DI in SAS Programming

how to Load Row Number in SAS DI

Helannivas — Mon, 24 Jun 2013 17:27:18 GMT

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.

Re: how to Load Row Number in SAS DI

jayachandra84_gmail_com — Mon, 24 Jun 2013 18:08:45 GMT

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)..

Re: how to Load Row Number in SAS DI

Helannivas — Tue, 25 Jun 2013 08:06:44 GMT

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?

Re: how to Load Row Number in SAS DI

Gaurang_sas — Tue, 25 Jun 2013 08:48:49 GMT

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

Re: how to Load Row Number in SAS DI

deleted_user — Tue, 25 Jun 2013 09:36:58 GMT

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

Re: how to Load Row Number in SAS DI

Helannivas — Wed, 26 Jun 2013 08:35:12 GMT

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.

Re: how to Load Row Number in SAS DI

deleted_user — Wed, 26 Jun 2013 08:53:07 GMT

Try this coalescec('Name',' ')

Re: how to Load Row Number in SAS DI

Helannivas — Wed, 26 Jun 2013 09:02:30 GMT

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