number of months that can be divisive by 12

[skyland1991]

Hi,

I am fairly new to SAS and I have a project that I need to figure out how to find the number of months that can be divisive by 12. For example, if the stock has 76 months of data available, I need to round down to 72 months by removing the oldest 4 months of data. Any help would be greatly appreciated. Thanks.

[Patrick]

You could use the mod() function for this.

data test;
  month_count=76;
  month12=month_count-mod(month_count,12);
  output;
  stop;
run;

proc print data=test;
run;

[skyland1991]

Thank you Patrick. What if I have a consecutive number of months that look like this:

count

1

2

3

4

5

6

7

8

9

10

11

12

13

14

...

how do I get rid of month 13 and 14? Thank you.

[Patrick]

You will first have to figure out the max count. Would you need to do this by stock or in total (meaning if for one stock you've got 24 months but for another only 14 months then you would want to select only 12 months for both stocks)?

[skyland1991]

Thank you for the reply. I need to do this by stock. for example if one stock has 14 I need 12, and another has 26 I need 24.

[Patrick · Accepted solution]

Here you go.

data have;
  stock=2;
  do count=1 to 14;
    output;
  end;
  stock=1;
  do count=1 to 26;
    output;
  end;
  stop;
run;

proc sort data=have;
  by stock count;
run;

data max_count(keep=stock max_count);
  set have(keep=stock count);
  by stock count;
  if last.stock then
    do;
      max_count=count-mod(count,12);
      output;
    end;
run;

data want;
  merge have max_count; 
  by stock;
  if count>max_count then delete;
run;

proc print data=want;
run;

[Patrick]

Or if you are more a SQL kind-of guy (or if your source data resides in a data base)

proc sql;
  create table want as
  select l.*
  from 
    have l
    inner join
    (
      select stock, max(count)-mod(max(count),12) as max_count
      from have
      group by stock
    ) r
    on l.stock=r.stock and l.count<=r.max_count
  order by l.stock, l.count
  ;
quit;

[skyland1991]

Thank you so much for all your answers! They really help a lot!

[skyland1991]

Hi Patrick,

Sorry to bother you again. I have a follow-up question, suppose I have got the number of months that can be divisive by 12, how can I further calculate the average price for each successive 12-month period (eg, if I have 120 months of data, I would like to have 10 trailing 12-month average prices)? Thank you.