BookmarkSubscribeRSS Feed
owenwqp1
Obsidian | Level 7

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
4 REPLIES 4
umashankersaini
Quartz | Level 8

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

owenwqp1
Obsidian | Level 7

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.

UrvishShah
Fluorite | Level 6

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

umashankersaini
Quartz | Level 8


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

Thanks Urwish.

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
  • 4 replies
  • 548 views
  • 0 likes
  • 3 in conversation