to store cumulative in sas

[Aman4SAS]

  Hi ,

As i m looking for help in one query

I hav 2 variable named

month      sale

jan          2000

feb         1400

mar        2300

apr         3400

may        2700

jun          3200

july          800

aug           2900

sep           1700

oct           2100

nov           2600

dec           3200

Now i m looking output like:

month      sale   cumm_sale

jan          2000     2000

feb         1400      3400

mar        2300      5700

apr         3400      9100

may        2700     11800

jun          3200     15000

july          800       15800

aug           2900    18700

sep           1700    20400

oct           2100     22500

nov           2600     25100

dec           3200     28300

i hav tried it with proc freq but its not storing cumm value in out= given dataset name.

plz help

[Deryl · Accepted solution]

data your_data;

     set your_data;

     if _n_ =1 then cumm_sale=0;

     cumm_sale=cumm_sale+sale;

     retain cumm_sale;

run;

[Aman4SAS]

thanks a lot sir,,,,, can we do it with proc sql.....?

anyways thanku very much sir

[Reeza]

Its not worth the effort do it in SQL

[Aman4SAS]

yaa, i beg ur pardon but our client requirement is to do it with proc sql..

i m tring to use analytical sum,, but not able thnk correct logic..

[Reeza]

Are you doing it in SAS or in a SQL server then, e.g Oracle or MS SQL?

[Reeza]

What's your data like? Ie is month actually a character month or a date? If you convert it to a number you can do an inner join and get it but on large datasets it will be resource intensive

data test;

input month    sale;

cards;

1 2000

2 1400

3 2300

4 3400

5 2700

6 3200

7 800

8 2900

9 1700

10 2100

11 2600

12 3200

;

run;

proc sql;

create table want as

select t.month, t.sale, sum(t2.sale) as cum_sale

from test t

inner join test t2 on t.month>=t2.month

group by t.month, t.sale

order by t.month;

quit;

[Aman4SAS]

yaa i agree with u , but it will work only small dataset like given,, but if it will b repated like for 10yr then it will create prob, n in this given solution by u , i thnk sir it will do group same amout of sale...

anyways thank u very much to giv me logic, i m working on it..

thanks

[Aman4SAS]

sir plz guide me y i m getting wrong result with query

proc sql;

select id,sum(amt) as gtot from rbs group by id having monotonic()<=3 order by gtot desc;

quit;

query for top 3 cust who withdraw max amt from his account.

and is there anyother fuction like top(5) in sql to get top five sal , or top marks gainer student..

[Reeza]

I'm not familiar with the monotonic() function, it isn't supported by SAS so I don't tend to use it.

You can do what you want with a data step, but if you want all PROC SQL that won't work so I don't know the answer your question.

Also, it is a new question so you're better off posting it as a new question.

Good luck!

[Aman4SAS]

DATA RBS;

length name $20. month $20.;

INPUT id NAME$ MONTH$ AMT;

DATALINES;

101 ANUJ JAN 3455

101 ANUJ FEB 7567

101 ANUJ MAR 3456

101 ANUJ APR 6453

101 ANUJ MAY 3564

101 ANUJ JUN 6436

101 ANUJ JUL 6346

101 ANUJ AUG 3453

101 ANUJ SEP 5544

101 ANUJ OCT 6644

101 ANUJ NOV 5333

101 ANUJ DEC 5300

102 AMIT JAN 7577

102 AMIT FEB 7655

102 AMIT MAR 3477

102 AMIT APR 8776

102 AMIT MAY 8755

102 AMIT JUN 3877

102 AMIT JUL 3209

102 AMIT AUG 2300

102 AMIT SEP 5766

102 AMIT OCT 7655

102 AMIT NOV 4577

102 AMIT DEC 5500

103 PAWAN JAN 2300

103 PAWAN FEB 3499

103 PAWAN MAR 4767

103 PAWAN APR 3234

103 PAWAN MAY 2344

103 PAWAN JUN 2345

103 PAWAN JUL 2399

103 PAWAN AUG 3495

103 PAWAN SEP 5422

103 PAWAN OCT 3400

103 PAWAN NOV 3300

103 PAWAN DEC 2340

104 KUMAR JAN 2900

104 KUMAR FEB 3450

104 KUMAR MAR 3250

104 KUMAR APR 2750

104 KUMAR MAY 2850

104 KUMAR JUN 2930

104 KUMAR JUL 7500

104 KUMAR AUG 2850

104 KUMAR SEP 4260

104 KUMAR OCT 5420

104 KUMAR NOV 1750

104 KUMAR DEC 5860

105 DEEPAK JAN 4100

105 DEEPAK FEB 2900

105 DEEPAK MAR 2300

105 DEEPAK APR 2800

105 DEEPAK MAY 1900

105 DEEPAK JUN 3400

105 DEEPAK JUL 1400

105 DEEPAK AUG 2300

105 DEEPAK SEP 4100

105 DEEPAK OCT 3200

105 DEEPAK NOV 4300

105 DEEPAK DEC 6500

;

RUN;

proc sql;

select id,sum(amt) as gtot from rbs group by id having monotonic()<=3 order by gtot desc;

quit;

plz chk monotonic work in sas

[Aman4SAS]

as per my month was char, so plz let me is it right procedure here to convert it in number..?

data test;

input month $ sale;

datalines;

jan 2000

feb 1400

mar 2300

apr 3400

may 2700

jun 3200

july 800

aug 2900

sep 1700

oct 2100

nov 2600

dec 3200

;

run;

proc print; run;

data test(keep= mon_num sale);

set test;

if month='jan' then mon_num=1;

if month='feb' then mon_num=2;

if month='mar' then mon_num=3;

if month='apr' then mon_num=4;

if month='may' then mon_num=5;

if month='jun' then mon_num=6;

if month='july' then mon_num=7;

if month='aug' then mon_num=8;

if month='sep' then mon_num=9;

if month='oct' then mon_num=10;

if month='nov' then mon_num=11;

if month='dec' then mon_num=12;

run;

proc print;

var mon_num sale;

run;

proc sql;

create table want as

select t.mon_num, t.sale, sum(t2.sale) as cum_sale

from test t

inner join test t2 on t.mon_num>=t2.mon_num

group by t.mon_num, t.sale

order by t.mon_num;

select * from want;

quit;

[Aman4SAS]

r u nt interested to ans me or sud i wait?