How to recode sequential values base on the previous value by group

[binhle50]

I need to create "variable_want" base on "variable_have" by each "id" and with "year" sorted ascending as shown in the table below. 

variable_have has 4 values (missing, 0, 1, -9999) and variable_want should be created as shown below. I would highly appreciate if anyone can help.

Best,

Le

id	Year	variable_have	variable_want
X4T39JHGK	2012	1	1
X4T39JHGK	2013	0	1
X4T39JHGK	2014		1
X4T39JHGK	2015	-9999	1
X4T39JHGK	2016	1	1
EZQA42HWD	2015	0	0
EZQA42HWD	2016	0	0
EZQA42HWD	2017	0	0
EZQA42HWD	2018	1	1
EZQA42HWD	2019	0	1
EZQA42HWD	2020		1
EZQA42HWD	2021	-9999	1
APJV74TMX	2015	-9999	-9999
APJV74TMX	2016	0	-9999
APJV74TMX	2017	0	-9999
APJV74TMX	2018	1	1
APJV74TMX	2019	0	1
APJV74TMX	2020		1
APJV74TMX	2021	-9999	1
CRMGKSEZH	2015
CRMGKSEZH	2016
CRMGKSEZH	2017	0	0
CRMGKSEZH	2018	-9999	-9999
CRMGKSEZH	2019	0	-9999
CRMGKSEZH	2020		-9999
CRMGKSEZH	2021	1	1

[jimbarbour]

That shouldn't be overly difficult.  Have you tried using a Retained variable to hold (or the LAG function) the prior value and then just replace the existing value if and only if the current value is greater than the prior value?

Jim

[ballardw]

What is the explicit rule that makes the variable_want value for APJV74TMX and CRMGKSEZH stop being -9999 and switch to 1?

Programs implement rules. An example is insufficient without the rule(s) involved.

I could guess at least two different rules but you know what you want. So tell us the rule you are using to make the switches.

[binhle50]

Hi Jim,
I did not see your question: What is the explicit rule that makes the variable_want value for APJV74TMX and CRMGKSEZH stop being -9999 and switch to 1? Yes, stop being -9999 and switch to 1;
Best,
Le

[binhle50]

And Jim, only switch -9999 to 1 for all following year, not previous years. I got everything worked except this. Thanks so much for your help!

[jimbarbour]

---

@binhle50 wrote:
And Jim, only switch -9999 to 1 for all following year, not previous years. I got everything worked except this. Thanks so much for your help!

---

Isn't that the behavior of the program now?  I must not be understanding.

In the below, in which line numbers would the value of Variable_Want change?  And what value would Variable_Want change to?

Jim

[binhle50]

Hi Jim,
When I run the code, the line number 4, variable_want is still -9999, not 1 as shown in the table.
Also, the line 20 and 21. the variable_want=0, not . as shown in the table.
Did I do anything wrong?
Best,
Le

[binhle50]

The line 12, variable_want also =-9999, not 1 as shown.

[jimbarbour]

Thank you.

What is the difference between Line 4 and Line 12?  In Line 4 we want "1", but in Line 12, we want -9999.  Is it because for X4T39JHGK there is additional data whereas for APJV74TMX there is not?

In other words, is this what you want?

Jim

[binhle50]

Hi Jim,
All I want variable_want=1 for all lines 4,12,19.
Best,
Le

[jimbarbour]

---

@binhle50 wrote:
I want variable_want=1 for all lines 4,12,19.

---

OK, so like this, yes?

The code I posted should already be doing this.  Is it not working for you?  If it is not working, did you change the program?  If so, I would need to see the actual code that you are running in order to help you.  I would also want to see the log.

Is the data you are running different?  If so, can you post the data here that is giving you the incorrect values?

Jim

[binhle50]

Hi Jim,
Here is my actual code:
data want;
set cc_iv_sv;
by SubjectId_Latest _date_of_visit NOTSORTED;
retain _ich_hist_te _hcv_hist_te _hbv_hist_te _hiv_hist_te _invas_joint_proc_hist_te _hav_vacc_hist_te _hbv_vacc_hist_te _circum_te _hysterectomy_te
_family_hist_te _inhi_iti_his_te _inhi_immune_modulation_hist_te _joint_ble_hist_te _known_genetic_mutation_te
_cvad_te;
retain _ich_hist_ha _hcv_hist_ha _hbv_hist_ha _hiv_hist_ha _invas_joint_proc_hist_ha _hav_vacc_hist_ha _hbv_vacc_hist_ha _circum_ha _hysterectomy_ha
_family_hist_ha _inhi_iti_his_ha _inhi_immune_modulation_hist_ha _joint_ble_hist_ha _known_genetic_mutation_ha
_cvad_ha;
array dyn(*) _ich_hist _hcv_hist _hbv_hist _hiv_hist _invas_joint_proc_hist _hav_vacc_hist _hbv_vacc_hist _circum _hysterectomy
_family_hist _inhi_iti_his _inhi_immune_modulation_hist _joint_ble_hist _known_genetic_mutation
_cvad;
array dynb(*) _ich_hist_ddd _hcv_hist_ddd _hbv_hist_ddd _hiv_hist_ddd _invas_joint_proc_hist_ddd _hav_vacc_hist_ddd _hbv_vacc_hist_ddd _circum_ddd _hysterectomy_ddd
_family_hist_ddd _inhi_iti_his_ddd _inhi_immune_modulation_hist_ddd _joint_ble_hist_ddd _known_genetic_mutation_ddd
_cvad_ddd;
array dync(*) _ich_hist_te _hcv_hist_te _hbv_hist_te _hiv_hist_te _invas_joint_proc_hist_te _hav_vacc_hist_te _hbv_vacc_hist_te _circum_te _hysterectomy_te
_family_hist_te _inhi_iti_his_te _inhi_immune_modulation_hist_te _joint_ble_hist_te _known_genetic_mutation_te
_cvad_te;
array dynd(*) _ich_hist_ha _hcv_hist_ha _hbv_hist_ha _hiv_hist_ha _invas_joint_proc_hist_ha _hav_vacc_hist_ha _hbv_vacc_hist_ha _circum_ha _hysterectomy_ha
_family_hist_ha _inhi_iti_his_ha _inhi_immune_modulation_hist_ha _joint_ble_hist_ha _known_genetic_mutation_ha
_cvad_ha;
array dyne(*) _ich_hist_jk _hcv_hist_jk _hbv_hist_jk _hiv_hist_jk _invas_joint_proc_hist_jk _jkv_vacc_hist_jk _hbv_vacc_hist_jk _circum_jk _hysterectomy_jk
_family_hist_jk _inhi_iti_his_jk _inhi_immune_modulation_hist_jk _joint_ble_hist_jk _known_genetic_mutation_jk
_cvad_jk;
do i=1 to 15;
if dyn(i)= -9999 then dyne(i)=0.5; else dyne(i)=dyn(i);
if dyne(i)>dync(i) then do;
dynb(i)=dyn(i);
dynd(i)=dyn(i);
dync(i)=dyne(i);
end; else do; dynb(i)=dynd(i); end;
if last.subjectid_latest then do; call missing(dync(i), dynd(i));
end; end;
keep SubjectId_Latest _source _ich_hist _ich_hist_dyn _hcv_hist _hcv_hist_dyn _hiv_hist _hiv_hist_dyn
_invas_joint_proc_hist _invas_joint_proc_hist_dyn _hav_vacc_hist _hav_vacc_hist_dyn _hbv_vacc_hist
_hbv_vacc_hist_dyn _circum _circum_dyn _hysterectomy _hysterectomy_dyn _family_hist
_family_hist_dyn _inhi_iti_his _inhi_iti_his_dyn _inhi_immune_modulation_hist _inhi_immune_modulation_hist_dyn
_joint_ble_hist _joint_ble_hist_dyn;
RUN;

[jimbarbour]

@binhle50,

Would it be possible for you to use the SAS code icon when you post your program?  It is very hard to read otherwise.

Jim

[jimbarbour]

First:  Are all those versions of the array found in cc_iv_sv?  I suspect only Dyn is.  If so, it's probably best to define the other arrays as _TEMPORARY_ and not give them individual field names.

Second:  I don't see anything wrong immediately.  I can't test it because of course I don't have access to cc_iv_sv.  The only thing I can recommend is that you use the Debug option of the Data step and follow the code as it executes.  If you have Enterprise Guide you would click on the little bug to enable debugging (if it is not already enabled).  You have to have at least EG 7.1.3 I think (maybe it's 7.1.5) in order to use the EG debugger.

If you're using Display Manager ("PC SAS"), then you would add /Debug as part of your Data statement like so:

data want / debug;

If you're using SAS Studio, it's going to be roughly similar to Enterprise Guide, but I don't have access to SAS Studio at the moment, so I can't give you any instruction there.

If you're not familiar with the debugger, you can just Google something like "SAS debugger commands" and you should be able to get started.

Sorry I can't be of more assistance here, but I can't access cc_iv_sv, so there's not much more I can do.

Jim

[binhle50]

Hi Jim,
I can't thank you enough for taking your time to help me out. You are so helpful.
Also, that is my fault, the code that you sent did work perfectly. I run the code and checked it out, but I checked the wrong columns. This morning, I checked again and It works great.
Again, thanks a bunch!
Best,
Le