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 |
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
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
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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.