flagging variables, the first record, and all the records

[michtka]

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.

[Patrick]

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;

[michtka]

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.

[Haikuo]

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

[michtka]

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.

[michtka]

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;

[Haikuo · Accepted solution]

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

[michtka]

The PROC SQL is great.

Many thanks.

[michtka]

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;

[Haikuo]

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

Haikuo