topic Re: Computed column calculation with previous row values in New SAS User

Computed column calculation with previous row values

VimalManavalan — Mon, 26 Aug 2019 13:44:14 GMT

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

Re: Computed column calculation with previous row values

PeterClemmensen — Mon, 26 Aug 2019 13:52:49 GMT

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;

Re: Computed column calculation with previous row values

novinosrin — Mon, 26 Aug 2019 14:12:15 GMT

%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;

Re: Computed column calculation with previous row values

VimalManavalan — Mon, 26 Aug 2019 14:18:57 GMT

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

Re: Computed column calculation with previous row values

VimalManavalan — Mon, 26 Aug 2019 14:19:47 GMT

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

Re: Computed column calculation with previous row values

novinosrin — Mon, 26 Aug 2019 14:20:26 GMT

Hmm what do you mean by that?

Re: Computed column calculation with previous row values

VimalManavalan — Mon, 26 Aug 2019 14:39:04 GMT

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?

Re: Computed column calculation with previous row values

novinosrin — Mon, 26 Aug 2019 14:44:32 GMT

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?

Re: Computed column calculation with previous row values

PeterClemmensen — Mon, 26 Aug 2019 14:46:45 GMT

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

Re: Computed column calculation with previous row values

VimalManavalan — Mon, 26 Aug 2019 14:57:49 GMT

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

Re: Computed column calculation with previous row values

novinosrin — Mon, 26 Aug 2019 15:15:36 GMT

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;

Re: Computed column calculation with previous row values

Reeza — Mon, 26 Aug 2019 15:23:38 GMT

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

Re: Computed column calculation with previous row values

acordes — Tue, 04 Jan 2022 14:45:34 GMT

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;

Re: Computed column calculation with previous row values

acordes — Tue, 04 Jan 2022 14:55:41 GMT

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.