topic Re: calculate proportion in SAS Procedures

calculate proportion

sasphd — Tue, 21 Apr 2015 19:37:34 GMT

crsp_fundno	begdt	enddt	x	duration
139	19941230	19980629		1277
139	19980630	19991230		548
139	19991231	20000629	MLGE	181
139	20000630	20000831	LCGE	62
2761	19981231	19991230		364
2761	19991231	20000629	LCGE	181
2761	20000630	20020629	LCCE	729
2761	20020630	20080330	LCGE	2100
2761	20080331	20130331	LCGE	1826

I want to calculate the proportion of variable in colunm X using duration.

The table want

crsp_fundno	x	proportion
139	MLGE	0,74485597
139	LCGE	0,25514403
2761	LCGE	0,78980769
2761	LCCE	0,8010989

For example 0.74485597=181/(1277+548+181+62)

Re: calculate proportion

Reeza — Tue, 21 Apr 2015 19:46:09 GMT

Proc freq

Re: calculate proportion

sasphd — Tue, 21 Apr 2015 19:48:49 GMT

more details please

Re: calculate proportion

Steelers_In_DC — Tue, 21 Apr 2015 19:50:48 GMT

proc freq data='dataset';table x;run;

Re: calculate proportion

sasphd — Tue, 21 Apr 2015 19:57:20 GMT

Thanks how can I get the output from this??????

Re: calculate proportion

Steelers_In_DC — Tue, 21 Apr 2015 20:04:37 GMT

run this code, you should get what you are looking for:

data have;

infile cards dsd;

input crsp_fundno begdt enddt x $ duration;

cards;

139,19941230,19980629,,1277

139,19980630,19991230,,548

139,19991231,20000629,MLGE,181

139,20000630,20000831,LCGE,62

2761,19981231,19991230,,364

2761,19991231,20000629,LCGE,181

2761,20000630,20020629,LCCE,729

2761,20020630,20080330,LCGE,2100

2761,20080331,20130331,LCGE,1826

;

run;

proc freq data=have;table x / out=want;run;

Re: calculate proportion

sasphd — Tue, 21 Apr 2015 20:06:14 GMT

proc freq data=cc;by crsp_fundno;

table crsp_fundno lipper_class / norow nocol nopercent

out= outfreq ;run;

this program does not give what I want !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

it gives me this

139		2	.
139	LCGE	1	50
139	MLGE	1	50
1063		2	.
1063	LCGE	1	50
1063	MLGE	1	50
2218		2	.
2218	LCGE	1	100
2761		2	.
2761	LCCE	1	25
2761	LCGE	3	75

but I want

crsp_fundno	x	proportion
139	MLGE	0,74485597
139	LCGE	0,25514403
2761	LCGE	0,78980769
2761	LCCE	0,8010989

Re: calculate proportion

sasphd — Tue, 21 Apr 2015 20:13:31 GMT

I did not want the freq but I want the proportion in funtion of the variable duration

crsp_fundno	begdt	enddt	x	duration
139	19941230	19980629		1277
139	19980630	19991230		548
139	19991231	20000629	MLGE	181
139	20000630	20000831	LCGE	62
2761	19981231	19991230		364
2761	19991231	20000629	LCGE	181
2761	20000630	20020629	LCCE	729
2761	20020630	20080330	LCGE	2100
2761	20080331	20130331	LCGE	1826

I want to calculate the proportion of variable in colunm X using duration.

The table want

crsp_fundno	x	proportion
139	MLGE	0,74485597
139	LCGE	0,25514403
2761	LCGE	0,78980769
2761	LCCE	0,8010989

For example 0.74485597=181/(1277+548+181+62)

Re: calculate proportion

Reeza — Tue, 21 Apr 2015 20:22:53 GMT

How does that work for those last two records?

At any rate you want some variation of the following. It works for your first example but not sure how you did the last two percentages.

Re: calculate proportion

Steelers_In_DC — Tue, 21 Apr 2015 20:27:34 GMT

Looks like your math is off, not sure if this is what you want but try this:

data have;

infile cards dsd;

input crsp_fundno begdt enddt x $ duration;

cards;

139,19941230,19980629,,1277

139,19980630,19991230,,548

139,19991231,20000629,MLGE,181

139,20000630,20000831,LCGE,62

2761,19981231,19991230,,364

2761,19991231,20000629,LCGE,181

2761,20000630,20020629,LCCE,729

2761,20020630,20080330,LCGE,2100

2761,20080331,20130331,LCGE,1826

;

run;

proc sql;

create table start as

select *,sum(duration) as SumDuration

from have

group by crsp_fundno;

data want;

set start;

prop = duration / SumDuration;

run;

Re: calculate proportion

sasphd — Tue, 21 Apr 2015 20:49:10 GMT

yes I did not understand how you obtain the proportion for crsp_fundno=2761

proportion LCCE=729/(364+181+729+2100+1826)= 0.1401

sorry i have done a mistake these are the right proportion

crsp_fundno	begdt	enddt	lipper_class	duration	proportion
139	19991231	20000629	MLGE	181	0,08752418
139	20000630	20000831	LCGE	62	0,02998066
2761	19991231	20000629	LCGE	181	0,78980769
2761	20000630	20020629	LCCE	729	0,14019231

Re: calculate proportion

sasphd — Tue, 21 Apr 2015 20:57:41 GMT

Ok mark

your program work, but I want the sum of proportion of LCGE for the crsp_fund 2761

Re: calculate proportion

Steelers_In_DC — Tue, 21 Apr 2015 21:06:34 GMT

See if this gives you what you want, I added x to the group by line:

proc sql;

create table start as

select *,sum(duration) as SumDuration

from have

group by crsp_fundno,x;

data want;

set start;

prop = duration / SumDuration;

run;

Re: calculate proportion

Reeza — Tue, 21 Apr 2015 22:37:24 GMT

Isn't Mark's answer correct? Why did you mark your own correct when it doesn't calculate a proportion which was your original question?

Re: calculate proportion

sasphd — Tue, 21 Apr 2015 23:51:26 GMT

the answer of Mark is good but not complete, but it was very useful. It dosen't give me what I want it exactly. I complete it by my answer.

But if you want that i mark it as correct without adding my part it dosen't give the right anwer!