Add flag=1 when condition true and ID in same group

[brulard]

hi,

I have dataset containing  IDs, with different rank, count, and dates. 

If you could offer a tip to meet the following: if condition true, then flag 1 for all same IDs.

Example HAVE:

Id  Rank Count Date

001  01     01      2017

001  02     01      9999

002  01     03     9999

003  01    02      2018

003  02    02     9999

004  01    02       9999

Example WANT

New Var Conditions: 

midflag:where year 9999 and count<3  /*optional variable*/

endflag:where midlflag=1 and ID (appears once or is duplicate from different row) 

Id  Rank Count Date       MIDflag  Endflag

001  01     01      2017      0           1

001  02     01      9999      1           1

002  01     03     9999      0            0

003  01    02      2018      0            1

003  02    02     9999      1           1

004  01    02       9999     1         1

Thanks in advance

[Reeza]

Is the count in the criteria (count<3) the same as the count in the data set, which looks to be a character variable (leading zeros?)?

[brulard]

it is a number formatted 1.

[mkeintz · Accepted solution]

You can interleave the dataset with itself on an ID by ID basis, such that for each id:

1. The subset of cases with date=9999 and count<3 are read an extra time prior to reading all cases for the id.  If any such preliminary observations are found, then ENDFLAG must be 1
1. RETAIN the ENDFLAG
2. but don't keep the above records (they are going to be re-read).  Hence the subsetting IF statement  "IF INKEEP;"
2. Followed by reading all cases for the ID, with MIDFLAG set record by record.

data have;
  input Id  Rank Count Date;
datalines;
001  01    01     2017
001  02    01     9999
002  01    03     9999
003  01    02     2018
003  02    02     9999
004  01    02     9999
run;

data want;
  set have (where=(date=9999 and count<3) in=inmid)
      have (in=inkeep);
  by id;

  if first.id then ENDflag=0;
  if inmid then ENDflag=1;
  retain endflag;

  if inkeep;
  if date=9999 and count<3 then midflag=1;
  else midflag=0;
run;

[brulard]

hi,
thank you for the tip, + explanation... as an aside, if there are any key books or articles you have to recommend, please let me know!

[novinosrin]

data have;
  input Id  Rank Count Date;
datalines;
001  01    01     2017
001  02    01     9999
002  01    03     9999
003  01    02     2018
003  02    02     9999
004  01    02     9999
run;

proc sql;
create table want1 as
select *,(date=9999 and count<3) as MIDflag,max(calculated MIDflag) as Endflag
from have
group by Id
order by id, rank;
quit;

[brulard]

thank you for the tip! very helpful