firm with the same name but different id number in different years, how can I change the id into the same one

Reply
Occasional Contributor
Posts: 17

firm with the same name but different id number in different years, how can I change the id into the same one

I have some firm data with more half a million firms. I found some firms with the same name have different id numbers in different years. I comfirmed by firm address that the firm with the same name but with different id numbers is actually the same firm.

I wonder how I can change the id number to be the same for each unique firm. Thanks!  The sample is as follows:

yearidname
1996600535930ABC international
1997600535930ABC international
1998600535930ABC international
1999730135548ABC international
2000730135548ABC international
2001730135548ABC international
2002730135548ABC international
2003745995778ABC international
2004745995778ABC international
2005745995778ABC international
2006745995778ABC international
2007745995778ABC international
2008745995778ABC international
2009745995778ABC international
2010745995778ABC international
1996730415910TBL data inquiry
1997730415910TBL data inquiry
1998737993908TBL data inquiry
1999737993908TBL data inquiry
2000730415910TBL data inquiry
2001730415910TBL data inquiry
2002730415910TBL data inquiry
2003730415910TBL data inquiry
2004730415910TBL data inquiry
2005730415910TBL data inquiry
2006730415910TBL data inquiry
2007737993908TBL data inquiry
2008737993908TBL data inquiry
2009737993908TBL data inquiry
2010737993908TBL data inquiry
Frequent Contributor
Posts: 118

Re: firm with the same name but different id number in different years, how can I change the id into the same one

Hi,

if you are confirmed with the ID number then :

the easiest and straight way is to use if then statement with data step.

if name ='ABC international' then id = 'XXXXXXXXXX' ;

this would make the unique id for ABC international.

if not satisfied with this..... we have lots of options to change to unique.

just let us know.

Regards

Uma Shanker Saini

Occasional Contributor
Posts: 17

Re: firm with the same name but different id number in different years, how can I change the id into the same one

Posted in reply to umashankersaini

Thanks Uma!

     But actually, some names also have some slight differences, mainly due to misspelling. I have changed the names into the same if they share the same id number. My problem is how to change the remaining observations with some differences in both id and name.

Regular Contributor
Posts: 195

Re: firm with the same name but different id number in different years, how can I change the id into the same one

Hi,

I assumed first ID(that is ID of 1996 in this case) will be same to all different Firms...

proc sort data = have;

  by name;

run;

data temp(keep = id name);

  set have;

  by name;

  if first.name then output;

run;

data want(drop = id);

  retain year id unique_id name;

  merge have temp(rename = (id = unique_id));

  by name;

run;

-Urvish

Frequent Contributor
Posts: 118

Re: firm with the same name but different id number in different years, how can I change the id into the same one


Agree with Urwish's code for your concern. Try it

Thanks Urwish.

Ask a Question
Discussion stats
  • 4 replies
  • 259 views
  • 0 likes
  • 3 in conversation