How do I create a cumulative column with multiple conditions?

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 17
Accepted Solution

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: 5,085

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;

View solution in original post


All Replies
Super User
Posts: 17,863

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

Occasional Contributor
Posts: 17

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: 17,863

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...

Occasional Contributor
Posts: 17

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: 17,863

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

Post your code.

 

Occasional Contributor
Posts: 17

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: 17,863

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. 

Occasional Contributor
Posts: 17

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: 5,085

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;

Occasional Contributor
Posts: 17

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

Thanks for your reply. I ran your code but its just giving three zero for the first three observations. Smiley Sad
Super User
Posts: 5,085

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.

Occasional Contributor
Posts: 17

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

You were right! I had a typo in my code! Your code works perfectly fine. Thank you so much!
Super User
Posts: 17,863

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;
Occasional Contributor
Posts: 17

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
  • 720 views
  • 4 likes
  • 3 in conversation