Help using Base SAS procedures

calculate proportion

Reply
Frequent Contributor
Posts: 118

calculate proportion

crsp_fundnobegdtenddtxduration
13919941230199806291277
1391998063019991230548
1391999123120000629MLGE181
1392000063020000831LCGE62
27611998123119991230364
27611999123120000629LCGE181
27612000063020020629LCCE729
27612002063020080330LCGE2100
27612008033120130331LCGE1826

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

The table want

crsp_fundnoxproportion
139MLGE0,74485597
139LCGE0,25514403
2761LCGE0,78980769
2761LCCE0,8010989

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

Super User
Posts: 17,840

Re: calculate proportion

Proc freq

Frequent Contributor
Posts: 118

Re: calculate proportion

more details please

Valued Guide
Posts: 858

Re: calculate proportion

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

Frequent Contributor
Posts: 118

Re: calculate proportion

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

Valued Guide
Posts: 858

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: 118

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

1392.
139LCGE150
139MLGE150
10632.
1063LCGE150
1063MLGE150
22182.
2218LCGE1100
27612.
2761LCCE125
2761LCGE375

but I want

crsp_fundnoxproportion
139MLGE0,74485597
139LCGE0,25514403
2761LCGE0,78980769
2761LCCE0,8010989
Frequent Contributor
Posts: 118

Re: calculate proportion

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

crsp_fundnobegdtenddtxduration
13919941230199806291277
1391998063019991230548
1391999123120000629MLGE181
1392000063020000831LCGE62
27611998123119991230364
27611999123120000629LCGE181
27612000063020020629LCCE729
27612002063020080330LCGE2100
27612008033120130331LCGE1826

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

The table want

crsp_fundnoxproportion
139MLGE0,74485597
139LCGE0,25514403
2761LCGE0,78980769
2761LCCE0,8010989

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

Super User
Posts: 17,840

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.

SASPic2.jpg

Frequent Contributor
Posts: 118

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_fundnobegdtenddtlipper_classdurationproportion
1391999123120000629MLGE1810,08752418
1392000063020000831LCGE620,02998066
27611999123120000629LCGE1810,78980769
27612000063020020629LCCE7290,14019231
Valued Guide
Posts: 858

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: 118

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: 858

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: 17,840

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: 118

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!

Ask a Question
Discussion stats
  • 14 replies
  • 505 views
  • 4 likes
  • 3 in conversation