How do I replace one dataset's ID column with a second dataset's ID column?

[cosmid]

Hi,

Here is the layout for the first dataset with the ID column that I want to replace:

ID      value

123     01

123     02

123     03

130     01

131      01

131      02

133      01

136      01

136      02

136      03

Please note that the ID will change once the Value column starts at 01 again

Here is the second dataset with the ID column that I want:

ID

1

3

5

9

15

The updated dataset should be the following:

ID      value

1        01

1        02

1        03

3       01

5       01

5       02

9       01

15      01

15      02

15      03

Thanks

[ballardw]

What information in the two data sets tells us that the 130 is supposed to be replaced by the 3?

How many of these values do you have if this is just an example question? I am afraid that without some more to go on the ids in the second set are pretty much useless.

[FreelanceReinh]

Hi @cosmid,

So the first, second, third, ... ID in the first dataset (let's call it HAVE1) is replaced by the first, second, third, ... ID in the second dataset (HAVE2), respectively?

If so, try this:

data want(drop=_);
retain id; /* redundant if order of variables is not important */
set have1(rename=(id=_));
if value='01' then set have2;
run;

[data_null__ · Accepted solution]

Not sure why but here you go.

data one;
   input ID  value:$2.;
   cards;
123     01
123     02
123     03
130     01
131      01
131      02
133      01
136      01
136      02
136      03
;;;;
   run;
data two;
   input ID;
   cards;
1
3
5
9
15       
;;;;   
   run;

data want;
   set one;
   by id;
   if first.id then set two(rename=ID=XID);
   id = xid;
   drop xid;
   run;
proc print;
   run;

[Ksharp]

data one;
   input ID  value:$2.;
   cards;
123     01
123     02
123     03
130     01
131      01
131      02
133      01
136      01
136      02
136      03
;;;;
   run;
data two;
   input ID;
   cards;
1
3
5
9
15    
;;;;   
   run;

data key;
 set one;
 by id;
 if first.id;
 keep id;
run;
data fmt;
 retain fmtname 'fmt' type 'i';
 merge key(in=ina keep=id rename=(id=start)) two(rename=(id=label));
 if ina;
run;
proc format cntlin=fmt;
run;
data want;
 set one(rename=(id=_id));
 id=input(_id,fmt.);
run;