identify begin of month to current date

[hdg]

Thanks everyone for all your help. I have another query

I have the following data var date and flag. THere is a flag on certain dates for certain var in a month.

I want to create a column called agg flag which puts a 1 in all dates before and including the date that has the flag from the beginning of the month to the date which has a flag, grouped by var

Thanks for your help!

var	date	flag
ann	3/6/2012
ann	3/7/2012
ann	3/8/2012
ann	3/9/2012	1
joe	4/9/2012
joe	4/10/2012
joe	4/11/2012
joe	4/12/2012
joe	4/13/2012
joe	4/16/2012
joe	4/17/2012
joe	4/18/2012
joe	4/19/2012
joe	4/20/2012
joe	5/1/2012
joe	5/2/2012	1
joe	5/3/2012

For example

sector_code	date	flag	Agg flag
ann	3/6/2012		1
ann	3/7/2012		1
ann	3/8/2012		1
ann	3/9/2012	1	1
joe	4/9/2012
joe	4/10/2012
joe	4/11/2012
joe	4/12/2012
joe	4/13/2012
joe	4/16/2012
joe	4/17/2012
joe	4/18/2012
joe	4/19/2012
joe	4/20/2012
joe	5/1/2012		1
joe	5/2/2012	1	1
joe	5/3/2012

[Haikuo · Accepted solution]

A minor modification on my previous code will do:

data have;

infile cards truncover;

input var:$ date :mmddyy10. flag;

format date mmddyy10.;

_id=cats(var,year(date),week(date));

cards;

ann 3/6/2012

ann 3/7/2012

ann 3/8/2012

ann 3/9/2012 1

joe 4/9/2012

joe 4/10/2012

joe 4/11/2012

joe 4/12/2012

joe 4/13/2012

joe 4/16/2012

joe 4/17/2012

joe 4/18/2012

joe 4/19/2012

joe 4/20/2012

joe 5/1/2012

joe 5/2/2012 1

joe 5/3/2012

;

data want (drop=_:);

  do _n_=1 by 1 until (last._id);

  set have;

by _id;

  if flag=1 then do;

  add_flag=1;

leave;

  end;

  end;

  do _n_=1 by 1 until (last._id);

  set have;

by _id;

output;

  if flag=1 then

leave;

     end;

run;

proc print;run;

Haikuo

[Ksharp]

Here is another way.

data have;
infile cards truncover;
input var $ date :mmddyy10. flag;
format date mmddyy10.;
cards;
ann 3/6/2012
ann 3/7/2012
ann 3/8/2012
ann 3/9/2012 1
joe 4/9/2012
joe 4/10/2012
joe 4/11/2012
joe 4/12/2012
joe 4/13/2012
joe 4/16/2012
joe 4/17/2012
joe 4/18/2012
joe 4/19/2012
joe 4/20/2012
joe 5/1/2012
joe 5/2/2012 1
joe 5/3/2012
;
run;
proc sort data=have(where=(flag=1)) out=temp ;by var descending date;run;
proc sort data=temp nodupkey;by var;run;
data x(keep=var _date);
 set temp;
 do _date=mdy(month(date),1,year(date)) to date;
  output;
 end;
 format _date mmddyy10.;
run;
data want;
 merge have(in=a) x(rename=(_date=date) in=b);
 by var date;
 aggflag=ifn(b,1,.);
 if a;
run;

Ksharp