Assigning a new id based on a set of condition

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 11
Accepted Solution

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;

View solution in original post


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

Respected Advisor
Posts: 4,640

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

how about this:

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;

Respected Advisor
Posts: 3,124

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;

/*if already sorted by ID*/

  data want_merge;

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

by id;

drop _:;

run;

Haikuo

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 397 views
  • 0 likes
  • 5 in conversation