Hi Everyone, I've got the next dataset have:
data have;
length visit $4;
length subjid aeflag injflag 8;
input subjid visit aeflag injflag;
datalines;
1 day1 . 1
1 day2 . 1
1 day3 1 1
1 day4 1 1
1 day5 1 .
2 day1 . .
2 day2 . .
2 day3 . .
2 day4 . 1
2 day5 1 1
3 day1 . .
3 day2 . 1
3 day3 . 1
3 day4 . 1
3 day5 . .
;
run;
and I would like a dataset want, flaging the variable aeflag for all the records of subjects are qualify, it means subjid 1 and 2, and I would like flag the variable injlesflag only the first record (day1) of subjects are qualify, it means 1, 2 and 3.
Any help?
Thanks.
V.
Classic 2XDOW:
data want;
do until (last.subjid);
set have;
by subjid;
_aef=ifn(aeflag=1,1,_aef);
_inf=ifn(injflag=1,1,_inf);
end;
do until (last.subjid);
set have;
by subjid;
aeflag=ifn(_aef=1,1,aeflag);
injflag=ifn(first.subjid,_inf,.);
output;
end;
drop _:;
run;
Or Proc SQL if you like:
proc sql;
select subjid, visit, case when sum(_aef)>=1 then 1 else _aef end as aeflag,
case when sum(_inf)>=1 and monotonic()=min(monotonic()) then 1 else . end as injflag
from have (rename=(aeflag=_aef injflag=_inf)) group by subjid order by subjid,visit;quit;
Haikuo
Not sure if I understood what you're actually asking for. I hope below code will give you the idea how to achieve what you're after:
data want;
set have;
by subjid visit;
if subjid in (1,2) then aeflag =1;
if first.subjid then injlesflag =1;
run;
Thanks Patrick, is not exactly it..I try to explain it better:
Rule to populate AEFLAG (many records by subject if subject is qualify):
Subject 1, show aeflag in days 3, 4 and 5, then because this subject have adverse events (subject qualify)...want to populate the rest of the days (1 and 2) also.
Subject 2, show adverse events day 5, therefore, want to populate the rest of the days too...Day 1, 2, 3 and 4.
Subject 3, dont show any adverse events, therefore is fine, it not need to be populated.
Rule to populate INJFLAG (1 record by subject if subject is qualify):
Subject 1, show injflag days 1 2 3 and 4, then, because this subject have injections (subject qualify)...want to populate only one record, his
first day (day 1).
Subject2, show inflag days 4 and 5, again I want to populate his first day (day 1).
Subject 3, show inflag 2,3, 4, then i want to populate his first day (day 1) only.
I hope it helps.
Thanks.
Your rule on 'aeflag' is clear, while on 'injflag' is still vague, as you did not say what to do with rest of the record except the first one in the group. You want to leave them as is or you want to make them missing? The following example is to leave them unchanged, while can be easily tweaked to make them missing. Your question is usually addressed by using 2XDOW, which requires 2 passes. The following code uses Hash(), so only 1 pass is needed, hopefully is more efficient if you have a huge data set.
data have;
length visit $4;
/*length subjid aeflag injflag 8;*/
input subjid visit aeflag injflag;
datalines;
1 day1 . 1
1 day2 . 1
1 day3 1 1
1 day4 1 1
1 day5 1 .
2 day1 . .
2 day2 . .
2 day3 . .
2 day4 . 1
2 day5 1 1
3 day1 . .
3 day2 . 1
3 day3 . 1
3 day4 . 1
3 day5 . .
;
run;
data want;
declare hash h(multidata:'y');
h.definekey('subjid','visit');
h.definedata( 'subjid', 'visit','aeflag','injflag');
h.definedone();
declare hiter hi('h');
do until (last.subjid);
set have;
by subjid;
_aef=ifn(aeflag=1,1,_aef);
_inf=ifn(injflag=1,1,_inf);
h.add();
end;
_rc=hi.first();injflag=ifn(_inf=1,1,injflag);
do _rc=0 by 0 while (_rc=0);
aeflag=ifn(_aef=1,1,aeflag);
output;
_rc=hi.next();
end;
drop _:;
run;
Haikuo
Hi Hai.kuo,
injflag: the rest of the records have to be missing, yes.
Please, can you re-write your code not using the hash function...is a bit complex for me,
I would like you to make it more simple if it is possible.
Thanks.
Hi Hai.kuo, I thought to make this approach:
proc sort data=have out=haves;
by subjid;
run;
*selecting subjid that show at least one injection;
proc sort data=have out=injection nodupkey;
by subjid;
where injflag ne .;
run;
data want;
merge haves(in=a keep=subjid visit aeflag) injection (in=b keep=subjid);
by subjid;
if b and first.subjid then injflag=1;
run;
Classic 2XDOW:
data want;
do until (last.subjid);
set have;
by subjid;
_aef=ifn(aeflag=1,1,_aef);
_inf=ifn(injflag=1,1,_inf);
end;
do until (last.subjid);
set have;
by subjid;
aeflag=ifn(_aef=1,1,aeflag);
injflag=ifn(first.subjid,_inf,.);
output;
end;
drop _:;
run;
Or Proc SQL if you like:
proc sql;
select subjid, visit, case when sum(_aef)>=1 then 1 else _aef end as aeflag,
case when sum(_inf)>=1 and monotonic()=min(monotonic()) then 1 else . end as injflag
from have (rename=(aeflag=_aef injflag=_inf)) group by subjid order by subjid,visit;quit;
Haikuo
The PROC SQL is great.
Many thanks.
My final contribution using my approach (I know there are many steps but still work);
*sorting by subjid;
proc sort data=have out=haves;
by subjid;
run;
*subjects with at least an injection;
proc sort data=have out=injection nodupkey;
by subjid;
where injflag ne .;
run;
data new1;
merge haves(in=a keep=subjid visit aeflag) injection (in=b keep=subjid);
by subjid;
if b and first.subjid then injflag=1;
run;
*subjects with at least an adverse event;
proc sort data=have out=adverse nodupkey;
by subjid;
where aeflag ne .;
run;
data new2;
merge haves(in=a keep=subjid visit aeflag) adverse (in=b keep=subjid);
by subjid;
if b then aeflag=1;
run;
*put together to create final dataset;
data want;
merge new1(in=a) new2(in=b keep=subjid aeflag);
by subjid;
run;
Good for you. Glad that you are making serious commitments and solid progress.
Haikuo
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.