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

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

brulard — Tue, 03 Apr 2018 19:09:30 GMT

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

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

Reeza — Tue, 03 Apr 2018 20:53:56 GMT

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

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

mkeintz — Tue, 03 Apr 2018 21:45:49 GMT

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

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

RETAIN the ENDFLAG
but don't keep the above records (they are going to be re-read). Hence the subsetting IF statement "IF INKEEP;"

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;

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

novinosrin — Tue, 03 Apr 2018 22:01:45 GMT

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;

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

brulard — Wed, 04 Apr 2018 11:41:21 GMT

it is a number formatted 1.

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

brulard — Wed, 04 Apr 2018 12:49:00 GMT

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!

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

brulard — Wed, 04 Apr 2018 12:49:33 GMT

thank you for the tip! very helpful