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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.