Help using Base SAS procedures

identify begin of month to current date

Accepted Solution Solved
Reply
Frequent Contributor
Frequent Contributor
Posts: 79
Accepted Solution

identify begin of month to current date

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!

vardateflag
ann3/6/2012
ann3/7/2012
ann3/8/2012
ann3/9/20121
joe4/9/2012
joe4/10/2012
joe4/11/2012
joe4/12/2012
joe4/13/2012
joe4/16/2012
joe4/17/2012
joe4/18/2012
joe4/19/2012
joe4/20/2012
joe5/1/2012
joe5/2/2012                 1
joe5/3/2012

For example

sector_codedateflagAgg flag
ann3/6/2012 1
ann3/7/2012 1
ann3/8/2012 1
ann3/9/201211
joe4/9/2012
joe4/10/2012
joe4/11/2012
joe4/12/2012
joe4/13/2012
joe4/16/2012
joe4/17/2012
joe4/18/2012
joe4/19/2012
joe4/20/2012
joe5/1/2012 1
joe5/2/2012    11
joe5/3/2012

Accepted Solutions
Solution
‎04-12-2012 03:03 PM
Respected Advisor
Posts: 3,156

Re: identify begin of month to current date

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=_Smiley Happy;

  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

View solution in original post


All Replies
Solution
‎04-12-2012 03:03 PM
Respected Advisor
Posts: 3,156

Re: identify begin of month to current date

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=_Smiley Happy;

  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

Super User
Posts: 10,041

Re: identify begin of month to current date

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

🔒 This topic is solved and locked.

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

Discussion stats
  • 2 replies
  • 161 views
  • 0 likes
  • 3 in conversation