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)
Proc freq
more details please
proc freq data='dataset';table x;run;
Thanks how can I get the output from this??????
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;
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 |
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)
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.
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 |
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;
Ok mark
your program work, but I want the sum of proportion of LCGE for the crsp_fund 2761
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;
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?
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!
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 25. 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.