topic Re: Cumulative Percentage within a formatted variable in SAS Programming

Cumulative Percentage within a formatted variable

aditip — Wed, 12 Nov 2014 21:55:23 GMT

Hello forum,

I am trying to calculate cumulative percent for the variable prob for each of the bin level.

so for bin = 'a' with 4 counts, the cum % should be

0.2/4

(0.2+0.3)/4

and so on .. for each of the bin levels.

data test;

input bin $ prob;

cards;

a .2

a .3

a .6

a .8

b .5

b .6

b .7

b .8

c .1

c .4

c .5

c .7

c .8

.... ;

what I would like to see is ..

bin prob cum_per

a .2 0.05

a .3 0.125

a .6 0.275

a .8 0.475

b .5 0.125

b .6 0.275

b .7 0.45

b .8 0.65

c .1 0.02

c .4 0.08

c .5 0.1

c .7 0.14

c .8 0.16

Thanks in advance for you help!

Re: Cumulative Percentage within a formatted variable

Reeza — Wed, 12 Nov 2014 22:07:38 GMT

Inelegant solution, but it works:

data test;

input bin $ prob;

cards;

a .2

a .3

a .6

a .8

b .5

b .6

b .7

b .8

c .1

c .4

c .5

c .7

c .8

.... ;

proc sql;

create table temp as

select a.bin, a.prob, count(bin) as tot

from test a

group by bin

order by bin, prob;

quit;

data want;

set temp;

retain cum_prob;

by bin;

if first.bin then cum_prob=prob/tot;

else cum_prob=cum_prob+(prob/tot);

run;

Re: Cumulative Percentage within a formatted variable

stat_sas — Wed, 12 Nov 2014 22:09:01 GMT

data test;
input bin $ prob;
cards;
a .2
a .3
a .6
a .8
b .5
b .6
b .7
b .8
c .1
c .4
c .5
c .7
c .8
;

data want;
set test;
by bin;
if first.bin then cumsum=0;
cumsum+prob;
run;

proc sql;
create table need as
select bin,prob,cumsum/count(bin) as cum_per from want
group by bin
order by bin,prob;
quit;

Re: Cumulative Percentage within a formatted variable

amats — Thu, 13 Nov 2014 00:31:55 GMT

data test;

input bin $ prob;

cards;

a .2

a .3

a .6

a .8

b .5

b .6

b .7

b .8

c .1

c .4

c .5

c .7

c .8

;

run;

data test2;

do until (last.bin);

set test;

by bin;

if first.bin then no=0;

no + 1;

end;

do until (last.bin);

set test;

by bin;

cum_per = sum(cum_per, (prob / no));

output;

end;

run;

Re: Cumulative Percentage within a formatted variable

Haikuo — Thu, 13 Nov 2014 01:00:17 GMT

A SQL approach (warning: not for the best efficiency):

data have;

input bin $ prob;

cards;

a .2

a .3

a .6

a .8

b .5

b .6

b .7

b .8

c .1

c .4

c .5

c .7

c .8

;

proc sql;

create table want as

select *, (select sum(prob) from have where bin=a.bin and prob <= a.prob )/count(bin) as cumsum

from have a

group by bin

order by a.bin, a.prob

;

quit;

Haikuo

Re: Cumulative Percentage within a formatted variable

Trancho — Thu, 13 Nov 2014 07:27:52 GMT

Hi another simple solution :

PROC SORT DATA=HAVE;

BY BIN;

RUN;

ODS LISTING CLOSE;

PROC TABULATE DATA=HAVE OUT=FREQ;

CLASS BIN / ASCENDING;

TABLE BIN;

RUN;

ODS LISTING;

DATA WANT;

MERGE TEST (IN=I) FREQ (KEEP=BIN N);

BY BIN;

IF I;

RETAIN CUM_PROB;

IF FIRST.BIN THEN CUM_PROB = 0;

CUM_PROB = CUM_PROB + PROB;

CUMM_PCT = CUM_PROB / N;

RUN;

Re: Cumulative Percentage within a formatted variable

Ksharp — Thu, 13 Nov 2014 13:34:03 GMT

data test;
input bin $ prob;
cards;
a .2
a .3
a .6
a .8
b .5
b .6
b .7
b .8
c .1
c .4
c .5
c .7
c .8
;
run;
data want;
n=0;
 do until(last.bin);
  set test;
  by bin;
  n+1;
 end;
sum=0;
 do until(last.bin);
  set test;
  by bin;
  sum+prob;cum_per=sum/n;output;
 end;
drop n sum;
run;

Xia Keshan