topic Re: flagging variables, the first record, and all the records in SAS Procedures

flagging variables, the first record, and all the records

michtka — Sat, 22 Dec 2012 02:29:56 GMT

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.

Re: flagging variables, the first record, and all the records

Patrick — Sat, 22 Dec 2012 03:24:13 GMT

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;

Re: flagging variables, the first record, and all the records

michtka — Sat, 22 Dec 2012 03:37:16 GMT

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.

Re: flagging variables, the first record, and all the records

Haikuo — Sat, 22 Dec 2012 04:34:16 GMT

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

Re: flagging variables, the first record, and all the records

michtka — Sat, 22 Dec 2012 04:42:16 GMT

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.

Re: flagging variables, the first record, and all the records

michtka — Sat, 22 Dec 2012 04:56:02 GMT

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;

Re: flagging variables, the first record, and all the records

Haikuo — Sat, 22 Dec 2012 04:56:35 GMT

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

Re: flagging variables, the first record, and all the records

michtka — Sat, 22 Dec 2012 05:00:33 GMT

The PROC SQL is great.

Many thanks.

Re: flagging variables, the first record, and all the records

michtka — Sat, 22 Dec 2012 05:10:48 GMT

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;

Re: flagging variables, the first record, and all the records

Haikuo — Sat, 22 Dec 2012 13:18:20 GMT

Good for you. Glad that you are making serious commitments and solid progress.

Haikuo