## Assigning a new id based on a set of condition

Solved
Occasional Contributor
Posts: 11

# Assigning a new id based on a set of condition

Hi

How do I get from point A to point B. The new_var variable is assigned on the following criteria:  Whenever the code= 'C', new_var= num; and assign this same values to other codes as long as they have the same Id. I'll prefer to use a data step in solving this problem as supposed to dividing data set A into 2 based on code ='C' with the new_var and then performing a join operation of this table to the other table with code (M,Z). I look forward to your solution/suggestion or comments

Thanks

A

Id   code  num  new_var

1      C     100

1      M    1990

1      M    2000

2      C     200

2      M    3654

2      M    7895

2      Z     9875

B

Id   code  num   new_var

1      C     100         100

1      M    1990        100

1      M    2000        100

2      C     200          200

2      M    3654         200

2      M   7895          200

2      Z    9875          200

Accepted Solutions
Solution
‎01-30-2013 03:29 PM
Super Contributor
Posts: 1,636

## Re: Assigning a new id based on a set of condition

Just taking out the flag

data work.have;

input Id  code \$ num;

cards;

1      C    100

1      M    1990

1      M    2000

2      C    200

2      M    3654

2      M    7895

2      Z    9875

;

data temp;

set have;

flag=ifc(upcase(code)='C',0,1); run;

proc sort data=temp; by id flag; run;

data want;

retain new_var;

set temp;

by id flag;  /* should be:  by id; */

new_var=ifn(first.id,num,new_var);

run;

proc print;run;

All Replies
Super Contributor
Posts: 1,636

## Re: Assigning a new id based on a set of condition

data have;

input Id   code \$ num;

cards;

1      C     100

1      M    1990

1      M    2000

2      C     200

2      M    3654

2      M    7895

2      Z     9875

;

data temp;

set have;

flag=ifc(upcase(code)='C',0,1);

proc sort data=temp;

by id flag;

run;

data want(drop=flag);

retain new_var;

set temp;

by id flag;

new_var=ifn(first.flag,num,new_var);

run;

proc print;run;

or

data have;

input Id   code \$ num;

cards;

1      C     100

1      M    1990

1      M    2000

2      C     200

2      M    3654

2      M    7895

2      Z     9875

;

/* code below works only when code=c,m,z */

data want;

retain new_var;

set have;

by id code notsorted;

new_var=ifn(first.code,num,new_var);

run;

proc print;run;

Occasional Contributor
Posts: 11

## Re: Assigning a new id based on a set of condition

Thanks for the reply however when I try both codes you listed the output I'm getting is what's shown below

Id  code  num  new_var

1  C       100      100

1  M       1990   1990

1  M       2000    1990

2  C       200      200

2  M      3654      3654

2  M      7895     3654

2   Z       9875    3654

However, this is what I would like

Id  code  num  new_var

1  C       100      100

1  M       1990   100

1  M       2000    100

2  C       200      200

2  M      3654      200

2  M      7895     200

2   Z       9875    200

Occasional Contributor
Posts: 11

## Re: Assigning a new id based on a set of condition

The code below does work. The only change I made was to replace the first.code with first.id. However, I still open to other suggestions

data work.have;

input Id  code \$ num;

cards;

1      C    100

1      M    1990

1      M    2000

2      C    200

2      M    3654

2      M    7895

2      Z    9875 ;

data temp;

set have;

flag=ifc(upcase(code)='C',0,1);

run;

proc sort data=temp;

by id flag;

run;

data want;

retain new_var;

set temp;

by id flag;

new_var=ifn(first.id,num,new_var);

run;

proc print;run;

Solution
‎01-30-2013 03:29 PM
Super Contributor
Posts: 1,636

## Re: Assigning a new id based on a set of condition

Just taking out the flag

data work.have;

input Id  code \$ num;

cards;

1      C    100

1      M    1990

1      M    2000

2      C    200

2      M    3654

2      M    7895

2      Z    9875

;

data temp;

set have;

flag=ifc(upcase(code)='C',0,1); run;

proc sort data=temp; by id flag; run;

data want;

retain new_var;

set temp;

by id flag;  /* should be:  by id; */

new_var=ifn(first.id,num,new_var);

run;

proc print;run;

Occasional Contributor
Posts: 11

## Re: Assigning a new id based on a set of condition

Thanks a lot. I just figured that as well.

Super Contributor
Posts: 1,636

## Re: Assigning a new id based on a set of condition

sorry!

the code below only works for your sample data:

data want;
retain new_var;
set have;
by id code notsorted;
new_var=ifn(first.id,num,new_var);
run;

proc print;run;
Obs    new_var    Id    code     num

1       100       1     C       100
2       100       1     M      1990
3       100       1     M      2000
4       200       2     C       200
5       200       2     M      3654
6       200       2     M      7895
7       200       2     Z      9875

It wound not work if you added the RED row:

data have;

input Id   code \$ num;

cards;

1      C     100

1      M    1990

1      M    2000

2      C     200

2      M    3654

2      M    7895

2      Z     9875

3      Z     9999

Posts: 5,519

## Re: Assigning a new id based on a set of condition

Try this way :

data have;

input Id   code \$  num;

datalines;

1      C     100

1      M    1990

1      M    2000

2      C     200

2      M    3654

2      M    7895

2      Z     9875

;

proc sort data=have; by id; run;

data want;

do until(last.id);

set have; by id;

if code = "C" then new_var = num;

end;

do until(last.id);

set have; by id;

output;

end;

run;

proc print; run;

PG

PG
Occasional Contributor
Posts: 11

## Re: Assigning a new id based on a set of condition

Thanks PG. Your code works perfect

Super Contributor
Posts: 578

## Re: Assigning a new id based on a set of condition

data have;

input id code \$ num;

cards;

1      C     100

1      M    1990

1      M    2000

2      C     200

2      M    3654

2      M    7895

2      Z     9875

run;

proc sql;

create table want as

select

t1.*,

t2.num as new_num

from

have t1

left outer join (select id, num from have where code='C') t2

on t1.id=t2.id

order by id, code;

quit;

Posts: 3,167

## Re: Assigning a new id based on a set of condition

At the risk of being obnoxious, here are another two options:

data have;

input Id code \$ num;

cards;

1 C 100

1 M 1990

1 M 2000

2 C 200

2 M 3654

2 M 7895

2 Z 9875

;

/*SQL*/

proc sql;

create table want_sql as

select *, max(case when code='C' then num else . end) as new from have group by id;quit;

data want_merge;

merge have have(where=(_code='C') rename=(num=new code=_code));

by id;

drop _:;

run;

Haikuo

🔒 This topic is solved and locked.