topic Re: to store cumulative in sas in SAS Procedures

to store cumulative in sas

Aman4SAS — Tue, 22 Jan 2013 16:52:58 GMT

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

Re: to store cumulative in sas

Deryl — Tue, 22 Jan 2013 17:01:47 GMT

data your_data;

set your_data;

if _n_ =1 then cumm_sale=0;

cumm_sale=cumm_sale+sale;

retain cumm_sale;

run;

Re: to store cumulative in sas

Aman4SAS — Tue, 22 Jan 2013 17:26:33 GMT

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

anyways thanku very much sir

Re: to store cumulative in sas

Reeza — Tue, 22 Jan 2013 17:58:06 GMT

Its not worth the effort do it in SQL

Re: to store cumulative in sas

Aman4SAS — Tue, 22 Jan 2013 18:10:23 GMT

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

Re: to store cumulative in sas

Reeza — Tue, 22 Jan 2013 18:25:16 GMT

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

Re: to store cumulative in sas

Reeza — Tue, 22 Jan 2013 18:39:22 GMT

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;

Re: to store cumulative in sas

Aman4SAS — Tue, 22 Jan 2013 18:51:13 GMT

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

Re: to store cumulative in sas

Aman4SAS — Tue, 22 Jan 2013 20:15:33 GMT

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;

Re: to store cumulative in sas

Aman4SAS — Tue, 22 Jan 2013 20:27:26 GMT

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

Re: to store cumulative in sas

Aman4SAS — Wed, 23 Jan 2013 17:24:36 GMT

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

Re: to store cumulative in sas

Reeza — Wed, 23 Jan 2013 18:41:47 GMT

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!

Re: to store cumulative in sas

Aman4SAS — Wed, 23 Jan 2013 18:54:52 GMT

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