## flagging variables, the first record, and all the records

Solved
Super Contributor
Posts: 301

# flagging variables, the first record, and all the records

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.

Accepted Solutions
Solution
‎12-21-2012 11:56 PM
Posts: 3,167

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

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

All Replies
Posts: 4,736

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

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;

Super Contributor
Posts: 301

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

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.

Posts: 3,167

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

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

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

Super Contributor
Posts: 301

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

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.

Super Contributor
Posts: 301

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

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;

Solution
‎12-21-2012 11:56 PM
Posts: 3,167

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

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

Super Contributor
Posts: 301

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

The PROC SQL is great.

Many thanks.

Super Contributor
Posts: 301

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

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;

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;

Posts: 3,167

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

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

Haikuo

🔒 This topic is solved and locked.