Solved
Contributor
Posts: 40

# How do I create a cumulative column with multiple conditions?

Hello everyone,

I am a newbie to SAS and desperately need help on something. I am trying to create an accumulated variable by another variable with more than one conditions. Here is an example of my dataset.

 year Company-id ACCREC 2000 12423 200 2001 12423 250 2002 12423 100 2003 12423 300 2004 12423 200 2005 12423 400 2000 17862 150 2001 17862 500 2002 17862 250 2003 17862 200 2004 17862 200 2005 17862 350

The target variable that I am trying to create is a cumulative total of variable ACCREC for each company and every 3 years. For example for the above example the results must be as follows:

 year Company-id ACCREC Target variable 2000 12423 200 0 2001 12423 250 0 2002 12423 100 550 2003 12423 300 650 2004 12423 200 600 2005 12423 400 900 2000 17862 150 0 2001 17862 500 0 2002 17862 250 900 2003 17862 200 950 2004 17862 200 650 2005 17862 350 750

For example for the company-id 12423 in years 2002 and 2003, the calculation of target variable is 550=100+250+200 and 650=300+100+250, respectively.

I have tried to use first.variable and multiple conditions but had no success so far. Any help would be highly appreciated.

Accepted Solutions
Solution
‎09-02-2016 05:48 PM
Super User
Posts: 6,785

## Re: How do I create a cumulative column with multiple conditions?

Here's an approach using just base SAS:

data want;

set have;

by company;

if first.company then counter=1;

else counter + 1;

total = accrec + lag(accrec) + lag2(accrec);

if counter < 3 then target_variable=0;

else target_variable = total;

drop counter total;

run;

All Replies
Super User
Posts: 23,773

## Re: How do I create a cumulative column with multiple conditions?

3 year running total.

Do you have SAS/ETS?

proc setinit;

run;

If you do, proc expand can easily do it. Otherwise looking for moving average via arrays and then take the sum rather than mean of the array.

http://support.sas.com/kb/41/380.html

Contributor
Posts: 40

## Re: How do I create a cumulative column with multiple conditions?

Yes I have SAS/ETS.

How can I do that with proc expand?

Super User
Posts: 23,773

## Re: How do I create a cumulative column with multiple conditions?

proc sort data=sashelp.stocks out=stocks;
by stock date;
run;

proc expand data=stocks out=stocks2;
id date;
by stock notsorted;
convert open=open_tot/ transformout=(movsum 3 trim 2);
run;

proc print data=stocks2 (obs=20);
run;

Your first obs in a N moving total (n-1) should be missing or 0 because at that point you don't have enough information to calculate a 3 year total...

Contributor
Posts: 40

## Re: How do I create a cumulative column with multiple conditions?

Thanks for the code. This seems to work fine except for the last two observations, which are missing for both company-id. For example for the first company, the code is giving missing value for the last two years instead of 600 and 900.
year Company-id ACCREC Target variable
2000 12423 200 0
2001 12423 250 0
2002 12423 100 550
2003 12423 300 650
2004 12423 200 600
2005 12423 400 900
Super User
Posts: 23,773

Contributor
Posts: 40

## Re: How do I create a cumulative column with multiple conditions?

proc sort data=have out=stocks;
by company-id year;
run;

proc expand data=stocks out=stocks2;
company-id year;
by company-id notsorted;
convert ACCREC=ACCREC_tot/ transformout=(movsum 3 trim 2);
run;
Super User
Posts: 23,773

## Re: How do I create a cumulative column with multiple conditions?

AmirSari wrote:
proc sort data=have out=stocks;
by company-id year;
run;

proc expand data=stocks out=stocks2;
company-id year;
by company-id notsorted;
convert ACCREC=ACCREC_tot/ transformout=(movsum 3 trim 2);
run;

What does your log say?

I would expect an error on the line:

company-id year;

it should be ID year, and I would probably sort your data so you can remove the notsorted option.

Contributor
Posts: 40

## Re: How do I create a cumulative column with multiple conditions?

The log doesn't give me any errors or warnings! Could it be the trim option?
Solution
‎09-02-2016 05:48 PM
Super User
Posts: 6,785

## Re: How do I create a cumulative column with multiple conditions?

Here's an approach using just base SAS:

data want;

set have;

by company;

if first.company then counter=1;

else counter + 1;

total = accrec + lag(accrec) + lag2(accrec);

if counter < 3 then target_variable=0;

else target_variable = total;

drop counter total;

run;

Contributor
Posts: 40

## Re: How do I create a cumulative column with multiple conditions?

Posted in reply to Astounding
Thanks for your reply. I ran your code but its just giving three zero for the first three observations.
Super User
Posts: 6,785

## Re: How do I create a cumulative column with multiple conditions?

Show what you ran ... there's probably a simple fix.  Maybe you coded <= 3 instead of < 3.

Contributor
Posts: 40

## Re: How do I create a cumulative column with multiple conditions?

Posted in reply to Astounding
You were right! I had a typo in my code! Your code works perfectly fine. Thank you so much!
Super User
Posts: 23,773

## Re: How do I create a cumulative column with multiple conditions?

It should have been trimleft rather than trim:

proc sort data=sashelp.stocks out=stocks;
by stock date;
run;

proc expand data=stocks out=stocks2;
id date;
by stock notsorted;
convert open=open_tot/ transformout=(movsum 3 trimleft 2);
run;

proc print data=stocks2 (obs=20);
run;
Contributor
Posts: 40

## Re: How do I create a cumulative column with multiple conditions?

Than fixed the problem. Thank you so much!
☑ This topic is solved.

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

Discussion stats
• 14 replies
• 1177 views
• 4 likes
• 3 in conversation