topic Re: substituting/ replacing a record with another in SAS Procedures

substituting/ replacing a record with another

Sandhya — Thu, 21 Jan 2010 20:29:16 GMT

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.

Re: substituting/ replacing a record with another

sbb — Thu, 21 Jan 2010 20:46:34 GMT

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.

Re: substituting/ replacing a record with another

data_null__ — Thu, 21 Jan 2010 21:09:27 GMT

[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]

Re: substituting/ replacing a record with another

MikeZdeb — Mon, 25 Jul 2011 18:35:00 GMT

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

Re: substituting/ replacing a record with another

Ksharp — Tue, 26 Jul 2011 04:50:58 GMT

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