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
data your_data;
set your_data;
if _n_ =1 then cumm_sale=0;
cumm_sale=cumm_sale+sale;
retain cumm_sale;
run;
data your_data;
set your_data;
if _n_ =1 then cumm_sale=0;
cumm_sale=cumm_sale+sale;
retain cumm_sale;
run;
thanks a lot sir,,,,, can we do it with proc sql.....?
anyways thanku very much sir
Its not worth the effort do it in SQL
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..
Are you doing it in SAS or in a SQL server then, e.g Oracle or MS SQL?
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;
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
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..
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!
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
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;
r u nt interested to ans me or sud i wait?
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: