BookmarkSubscribeRSS Feed
Helannivas
Quartz | Level 8

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.

7 REPLIES 7
jayachandra84_gmail_com
Calcite | Level 5

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
Quartz | Level 8

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
Fluorite | Level 6

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

deleted_user
Not applicable

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
Quartz | Level 8

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
Not applicable

Try this  coalescec('Name',' ')

Helannivas
Quartz | Level 8


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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 3069 views
  • 0 likes
  • 4 in conversation