topic Re: How do I replace one dataset's ID column with a second dataset's ID column? in New SAS User

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

cosmid — Tue, 04 Jun 2024 20:57:56 GMT

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:

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

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

ballardw — Tue, 04 Jun 2024 21:17:19 GMT

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.

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

FreelanceReinh — Tue, 04 Jun 2024 21:28:09 GMT

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;

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

data_null__ — Tue, 04 Jun 2024 21:30:32 GMT

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;

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

Ksharp — Wed, 05 Jun 2024 01:35:41 GMT

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;