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

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 123
Accepted Solution

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

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


Accepted Solutions
Solution
‎04-04-2018 08:47 AM
Trusted Advisor
Posts: 1,345

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

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;

 

 

    

View solution in original post


All Replies
Super User
Posts: 23,776

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

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

 

Frequent Contributor
Posts: 123

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

it is a number formatted 1.
Solution
‎04-04-2018 08:47 AM
Trusted Advisor
Posts: 1,345

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

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;

 

 

    

Frequent Contributor
Posts: 123

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

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!
PROC Star
Posts: 1,837

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

[ Edited ]
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;
Frequent Contributor
Posts: 123

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

Posted in reply to novinosrin
thank you for the tip! very helpful
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 125 views
  • 2 likes
  • 4 in conversation