topic Re: number of months that can be divisive by 12 in SAS Procedures

number of months that can be divisive by 12

skyland1991 — Sun, 05 Jul 2020 02:11:47 GMT

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.

Re: number of months that can be divisive by 12

Patrick — Sun, 05 Jul 2020 02:50:18 GMT

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;

Re: number of months that can be divisive by 12

skyland1991 — Sun, 05 Jul 2020 04:03:41 GMT

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

count

...

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

Re: number of months that can be divisive by 12

Patrick — Sun, 05 Jul 2020 04:11:42 GMT

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)?

Re: number of months that can be divisive by 12

skyland1991 — Sun, 05 Jul 2020 04:13:38 GMT

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.

Re: number of months that can be divisive by 12

Patrick — Sun, 05 Jul 2020 04:24:13 GMT

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;

Re: number of months that can be divisive by 12

Patrick — Sun, 05 Jul 2020 04:30:31 GMT

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;

Re: number of months that can be divisive by 12

skyland1991 — Sun, 05 Jul 2020 04:32:03 GMT

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

Re: number of months that can be divisive by 12

skyland1991 — Mon, 06 Jul 2020 22:07:24 GMT

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.