topic Re: How to recode sequential values base on the previous value by group in SAS Programming

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

binhle50 — Fri, 06 Aug 2021 15:05:17 GMT

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,

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

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

jimbarbour — Fri, 06 Aug 2021 15:08:16 GMT

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

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

ballardw — Fri, 06 Aug 2021 15:21:43 GMT

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.

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

jimbarbour — Fri, 06 Aug 2021 15:43:47 GMT

@binhle50,

I think the below program should give you what you want.

Jim

DATA	Want;
	DROP	_:;
	SET	Have;
		BY	ID Year	NOTSORTED;
	RETAIN	_Prior_Have;
	RETAIN	_Prior_Test;

	IF	Variable_Have			=	-9999		THEN
		_Test_Have				=	0.5;
	ELSE
		_Test_Have				=	Variable_Have;
	
	IF	_Test_Have				>	_Prior_Test	THEN
		DO;
			Variable_Want		=	Variable_Have;
			_Prior_Have			=	Variable_Have;
			_Prior_Test			=	_Test_Have;
		END;
	ELSE
		DO;
			Variable_Want		=	_Prior_Have;
		END;

	IF	LAST.ID									THEN
		DO;
			CALL	MISSING(_Prior_Test, _Prior_Have);
		END;
RUN;

Results:

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

binhle50 — Mon, 09 Aug 2021 15:20:54 GMT

Hi Jim,

Thanks so much for your help! I will give it a try and will let you know if It works.

Best,

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

binhle50 — Mon, 09 Aug 2021 16:29:51 GMT

Hi Jim,

I tried the code (see below) but it still did not work the way I want. If the first.variable_have=missing then variable_want should be missing (it =0 then I run the code). Also, if variable_have=1 then variable_want should=1 and all years after that variable_want should all =1 (it works for most of the time but it did not work when variable_have=-9999, the variable_want still=-9999, not =1 as I wanted.. Could you please help to review the code below to see if I did anything wrong?

I really appreciate your help!

Best,

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;

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

binhle50 — Mon, 09 Aug 2021 16:36:10 GMT

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

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

binhle50 — Mon, 09 Aug 2021 16:40:21 GMT

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!

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

jimbarbour — Mon, 09 Aug 2021 19:18:04 GMT

@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

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

binhle50 — Mon, 09 Aug 2021 20:41:49 GMT

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

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

binhle50 — Mon, 09 Aug 2021 20:43:29 GMT

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

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

jimbarbour — Mon, 09 Aug 2021 21:01:07 GMT

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

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

binhle50 — Mon, 09 Aug 2021 21:31:00 GMT

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

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

jimbarbour — Mon, 09 Aug 2021 21:37:21 GMT

@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

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

binhle50 — Mon, 09 Aug 2021 22:29:12 GMT

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;

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

jimbarbour — Mon, 09 Aug 2021 22:37:35 GMT

@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

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

jimbarbour — Mon, 09 Aug 2021 23:48:14 GMT

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

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

Patrick — Tue, 10 Aug 2021 02:25:49 GMT

@binhle50 Below code addressing your initial question.

Not sure what this array galore you've posted now is about but it appears you're now dealing with a totally different data structure. Please provide some sample data (via a SAS data step) and explain/show how the desired result needs to look like.

data have;
  infile datalines dsd truncover;
  input id:$9. Year variable_have variable_want;
  datalines;
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
;

proc sort data=have;
  by id year;
run;

/* variable_derived: retain value based on value priority */
proc format;
  invalue value_prio
    1     =1
    -9999 =2
    0     =3
    other =4
  ;
run;

data want;
  set have;
  by id year;
  retain variable_derived;

  if first.id then
    variable_derived=variable_have;
  else if input(variable_have,value_prio.)<input(variable_derived,value_prio.) then
    variable_derived=variable_have;
run;

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

binhle50 — Tue, 10 Aug 2021 13:30:09 GMT

Thanks so much Patrick, it is simple, straight forward but works great!

Best,

Le

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

binhle50 — Tue, 10 Aug 2021 13:35:08 GMT

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