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

[jimbarbour · Accepted solution]

OK!  Excellent!  👍👍  

If you would, please mark the solution so that this topic shows as "solved."

Thanks for hanging in there with me,

Jim

[jimbarbour]

@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:

[binhle50]

Hi Jim,

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

Best,

Le

[binhle50]

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,

Le

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;

[Patrick]

@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;

[binhle50]

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

Best,

Le