How to retain the values by judging the values from previous obversions?

[null]

Hi,

I need some help.

The last column "want" of below snapshot is what we want to derive. 

The rule is like this:

1. for 1st row, set vsstresn of that row as "want".

2. if the wchg (percent change of weight in unit %) is less than 10%, e.g. see row 2 - 4, set the vsstresn from the 1st row as wanted.

3. if the wchg (percent change of weight in unit %) is more than 10%, then keep the weight (vsstresn) of that row as "want", e.g. row 5. For row 6 - 7, wchg<10, so keep the weight from above row (row 5) as wanted.

Similar case for row 8 and 9.

Dummy code to be used is as below: 

data a;
	usubjid='101-001';
	ecstdtc='2022-11-01';vsstresn=90; wchg=0; output;
	ecstdtc='2022-11-02';vsstresn=91; wchg=1.1; output;
	ecstdtc='2022-11-03';vsstresn=92; wchg=1.1; output;
	ecstdtc='2022-11-04';vsstresn=93; wchg=1.1; output;
	ecstdtc='2022-11-05';vsstresn=120; wchg=29; output;
	ecstdtc='2022-11-06';vsstresn=121; wchg=0.83; output;
	ecstdtc='2022-11-07';vsstresn=122; wchg=0.83; output;
	ecstdtc='2022-11-08';vsstresn=140; wchg=14.8; output;
	ecstdtc='2022-11-09';vsstresn=141; wchg=0.7; output;
run;

Please help and advise, thanks a lot!

[Quentin]

Can you show the code you have tried? That will help people help you. Also describe whether you get errors from your code, or some sort of wrong result.

[PaigeMiller]

data b;
    set a;
    retain want;
    if _n_=1 then want=vsstresn;
    if wchg>10 then want=vsstresn;
run;

[ballardw]

An aside to the question: you will find in the long run that having "date" values as character strings is cumbersome, inefficient and often complicates any processing involving the date, or time/datetime values. You should attempt to make sure that your "date" values are SAS date values, i.e. numeric with an appropriate format for people to understand.

One reason given this example: suppose after you have your new data that you want to compute the average of that Want variable for each calendar month. With your current character date you would have to create new variables to use for grouping. If the date variable were an actual SAS date you can create the group in a procedure like Means just by changing the format to YYMON. No additional data step to add variables, no additional variables needed.

[ErikLund_Jensen]

Hi @null 

The following code gives the desired result with your test data, but it doesn't follow your rules. By looking at your input, i looks to me as the rules could also be expressed as:

Give a new value to want, when 

• actual row is the first row for any usubjid, or
• a new "group" of concecutive vsstresn values within a given usubjid is encountered, i.e. vsstresn ne previous vsstresn + 1.

data b (drop=oldstressn);
  set a;
  by usubjid;
  retain want;
  oldstressn = lag(vsstresn);
  if first.usubjid then want = vsstresn;
  else if vsstresn ne oldstressn + 1 then want = vsstresn;
run;