substituting/ replacing a record with another

[Sandhya]

Hi,

I have a data set tab129

CID STEP TCODE
001 1 aa000
001 1 aa002
001 2 aa000
001 2 aa001
001 2 aa003
001 9 aa000
001 9 aa004
002 1 aa000
002 1 aa002
002 1 aa003
002 1 aa004
002 9 aa002
002 9 aa005
002 9 aa006

and so on. Here value for step 9 is the event which is not been defined in step 1 or 2. I need the TCODE. I need to convert 9 into the maximum step number for that CID. (ie), for CID 001, 9 should be changed into 2 as that is the highest step for that CID, but for CID 002, 9 takes the value 1.

How to deal with this issue?

Thanks in advance,
Sandy.

[sbb]

It sounds as though you have the "process flow logic" and rules to manipulate the data values. With SAS, I would suggest you look at using either a matched-MERGE or UPDATE (using a DATA step technique, with your updates in a separate file).

Your task will be to develop a SAS DATA step approach (likely with using PROC SORT and maybe other PROCs) to get your "updates" created based on your stated criteria.

Suggested Google advanced search for this topic/post:

update master file site:sas.com


Scott Barry
SBBWorks, Inc.

[data_null__]

[pre]
data have;
input CID $ STEP TCODE $;
cards;
001 1 aa000
001 1 aa002
001 2 aa000
001 2 aa001
001 2 aa003
001 9 aa000
001 9 aa004
002 1 aa000
002 1 aa002
002 1 aa003
002 1 aa004
002 9 aa002
002 9 aa005
002 9 aa006
;;;;
run;
data need;
do until(last.cid);
set have;
by cid step;
if first.step and step ne 9 then s=step;
if step eq 9 then step=s;
output;
end;
run;
proc print;
run;
[/pre]

[MikeZdeb]

Hi ... just another thought on this.  If the observations are not sorted in step order

within a CID, you could try the following.  The first pass (first SET HAVE) finds the

largest value of STEP that is not 9.  The second pass replaces the 9s with

that largest value from the first pass.

data need;

do until (last.cid);

  set have (in=a where=(step ne 9)) have;

  by  cid;

  if a  then big = max(big,step);

  if ^a then do; step = ifn(step ne 9, step, big); output; end;

end;

drop big;

run;

Based on ...

Interleaving a Dataset with Itself: How and Why

by Howard Schreier

http://www.nesug.org/proceedings/nesug03/cc/cc002.pdf

[Ksharp]

data have;
   input CID $ STEP TCODE $;
   cards;
001 1 aa000
001 1 aa002
001 2 aa000
001 2 aa001
001 2 aa003
001 9 aa000
001 9 aa004
002 1 aa000
002 1 aa002
002 1 aa003
002 1 aa004
002 9 aa002
002 9 aa005
002 9 aa006
;;;;
   run;
data want(drop=_step);
 set have;
 retain _step;
 if step ne 9 then _step=step;
  else step=_step;
run;

Ksharp