OK! Excellent! 👍👍
If you would, please mark the solution so that this topic shows as "solved."
Thanks for hanging in there with me,
Jim
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:
Hi Jim,
Thanks so much for your help! I will give it a try and will let you know if It works.
Best,
Le
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;
@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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.