Computed column calculation with previous row values

[VimalManavalan]

hello everyone,

im trying to implement an easy excel formula to a SAS computed column. Can someone help me,

here is the formula with result shown in column C excel sheet screenshot, i would like to have Column C values as SAS computed Column

[PeterClemmensen]

Here is one way

data have;
input Cmonth $ count;
datalines;
Jan 3
Feb 1
Mar 3
Apr 5
May 6
Jun 7
Jul 2
Aug 1
Sep 3
Oct 5
Nov 6
Dec 8
;

data want(drop=obs);
    array lag[0:2] _temporary_;
    do obs=1 by 1 until (lr);
        set have end=lr;
        lag[mod(obs, 3)]=count;
        sum=sum(of lag[*]);
        output;
    end;
run;

[VimalManavalan]

Will it be possible to create a SAS computed  column for column C instead of running program?

[PeterClemmensen]

I'm not much of an Enterprise Guide user. But I doubt that you can implement this logic with a point-and-click tool

[novinosrin]

%let window=3;
data want;
 do _n_=1 to &window until(z);
  set have end=z;
  array t(&window) _temporary_;
  _iorc_+1;
  t(_n_)=count;
  if _iorc_>=&window then want=sum(of t(*));
  output;
 end;
run;
  

[VimalManavalan]

Will it be possible to create a SAS computed  column for column C instead of running program

[novinosrin]

Hmm what do you mean by that?

[VimalManavalan]

SAS enterprise guide has an option to create computed column using query builder, which is an easy way to create columns using SAS functons. screnshot attached.

it would be really great we can manage it via SAS computed column. is it possible?

[novinosrin]

Hi @VimalManavalan  Oh well, I haven't used much of the point-n-click SAS EG lately and you are likely to know more about it. For all I know, that generates and embedded SQL query. So if you want a SQL approach for the same, I can give you SQL code. However, you would still need a numeric Month or a monotonic() to get a sequence of months to get the window period. So, are you after the SQL approach?

[acordes]

I like it but I don't fully understand. 

Can you be so kind to explain? 

I understand all pieces of your code but I do not see how they unfold as a whole. 

I've problems in coming behind the logic of a set statenent within the do loop. 

[VimalManavalan]

If possible Please share SQL code, I will try to convert to SAS computed column once I  understand the logic thanks

[novinosrin]

Okay @VimalManavalan , if you can make month 1-12 in the form of numbers, addressing a window period is easy -->

Here 3 is the window period

proc sql;
create table want as
select a.*,ifn(count(*)>=3,sum(b.count),.) as want
from have a left join have b
on a.month-2<=b.month<=a.month
group by a.month,a.count
order by a.month;
quit;

Else you may have create a sequence with monotonic() function in an in-line view and then do the same

proc sql;
create table want(drop=m) as
select a.*,ifn(count(*)>=3,sum(b.count),.) as want
from (select *,monotonic() as m from have) a left join (select *,monotonic() as m from have) b
on a.m-2<=b.m<=a.m
group by a.m,a.month,a.count
order by a.m;
quit;

[Reeza]

Check if you have a license for SAS ETS. If you do, use PROC TIMESERIES or one of the time series data prep tasks to do this instead of manually coding it yourself. That will account for missing months for example which you won't catch with a data step. 

You can check what you have licensed in SAS using:
proc setinit; run;
And what's installed on your system using:
proc product_status; run;
The output will be in the log. 

---

@VimalManavalan wrote:

hello everyone,

im trying to implement an easy excel formula to a SAS computed column. Can someone help me,

here is the formula with result shown in column C excel sheet screenshot, i would like to have Column C values as SAS computed Column

---

[acordes]

data have2;
set have;
format date date9.;
date=input(cats('01',cmonth,'2021'),date9.);
run;

proc expand data=have2 method=none out=want;
id date;
convert count=sum_lag3 / tout=(movsum 3);
run;