## calculate proportion

Frequent Contributor
Posts: 126

# calculate proportion

 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)

Super User
Posts: 23,683

## Re: calculate proportion

Proc freq

Frequent Contributor
Posts: 126

Valued Guide
Posts: 863

## Re: calculate proportion

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

Frequent Contributor
Posts: 126

## Re: calculate proportion

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

Valued Guide
Posts: 863

## Re: calculate proportion

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;

Frequent Contributor
Posts: 126

## Re: calculate proportion

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
Frequent Contributor
Posts: 126

## Re: calculate proportion

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)

Super User
Posts: 23,683

## Re: calculate proportion

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.

Frequent Contributor
Posts: 126

## Re: calculate proportion

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
Valued Guide
Posts: 863

## Re: calculate proportion

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;

Frequent Contributor
Posts: 126

## Re: calculate proportion

Ok mark

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

Valued Guide
Posts: 863

## Re: calculate proportion

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;

Super User
Posts: 23,683

## Re: calculate proportion

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?

Frequent Contributor
Posts: 126

## Re: calculate proportion

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!

Discussion stats
• 14 replies
• 720 views
• 4 likes
• 3 in conversation