sort by multiple variable

[jojozheng]

Hi everyone, 

I have a dataset after I sorted by id date, results looks like below:

date   id     flag

JAN    1      0

FEB    1      0

JAN    2      1

FEB    2      1

JAN    3      0

FEB    3      1

I want to see:  first flag=1 group by id date. 

date   id     flag   flag1

JAN    1      0

FEB    1      0

JAN    2      1       1

FEB    2      1

JAN    3      0

FEB    3      1       1

My code failed:

data test;

set old;

by id date flag;

if first.flag=1 then flag1=1; 

anyone knows how to get data I want? Thank you so much! 

[novinosrin]

are you missing

retain flag1;

[jojozheng]

no. I just want to denote every first flag=1 corrurance and let it be flag1=1. 

[novinosrin]

is this your want?

date   id     flag   flag1

JAN    1      0

FEB    1      0

JAN    2      1       1

FEB    2      1

JAN    3      0

FEB    3      1       1

[jojozheng]

yes

[novinosrin]

data have;
input date $   id     flag;
cards;
JAN    1      0
FEB    1      0
JAN    2      1
FEB    2      1
JAN    3      0
FEB    3      1
;

data want;

set have;

by id  flag notsorted ;

if first.flag and flag=1 then flag1=1; 
run;

[Astounding]

That's fine as long as you have a maximum of 2 months per ID.  Once you hit 3 months per ID, you have to account for a flag pattern like 1 0 1.

[jojozheng]

I have a dataset with million dataset, I just set an example for the situation I try to get.

[jojozheng]

Thank you @novinosrin
result from your code
date id flag flag1

JAN 1 0

FEB 1 0

JAN 2 1 1

FEB 2 1 1

JAN 3 0

FEB 3 1 1


but how to not retain 1 for every id, I just need first flag=1 to be denoted. Thanks!

[Astounding · Accepted solution]

Try it this way:

data want;

set have;

by id;

retain dummy;

if first.id then dummy=flag;

else dummy + flag;

if dummy=1 then do;

   flag1=1;

   dummy=2;

end;

drop dummy;

run;

[mkeintz]

This one uses virtually the same logic as @Astounding.  I put it in for variety, and a possible unreasonable regard for compactness.

data have;
input date $   id     flag;
cards;
JAN    1      0
FEB    1      0
JAN    2      1
FEB    2      1
JAN    3      0
FEB    3      1
;

data want (drop=n_ones);
  set have;
  by id flag;

  if first.id then n_ones=0;
  n_ones+flag;
  flag1= ifn(flag=1 and n_ones=1,1,0);
run;    

[jojozheng]

Thank you!