BookmarkSubscribeRSS Feed
Sandhya
Fluorite | Level 6
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.
4 REPLIES 4
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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__
Jade | Level 19
[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
Rhodochrosite | Level 12

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
Super User
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

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • 1203 views
  • 0 likes
  • 5 in conversation