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
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;
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.
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;
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;
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;
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.