Hello,
I have a dataset like this;
key date indicator1
1 05/01/13 1
2 05/01/13 0
3 06/01/13 1
4 06/01/13 .
5 06/02/13 0
6 06/03/13 1
in dataset indicator 1 = present ( qualify as numerator) 0=(qualify as denominator) missing(.) = not applicable.
now I want to find respective numerator and denominator for the given month. my output should look like
key date indicator1 rate
1 05/01/13 1 1/2
2 05/01/13 0 1/2
3 06/01/13 1 2/3
4 06/02/13 0 2/3
5 06/03/13 1 2/3
I am wondering if somebody can help me on this.
Thanks
You can take the average of indicator1 then by month to get what you want, and use the fract8. format to get the fraction rather than a decimal.
It doesn't matter if you filter out the missing because they're not included in the average calculation anyways, but I did exclude them using a where clause.
data have;
informat date mmddyy10.;
format date date9.;
input key date mmddyy10. indicator1;
cards;
1 05/01/13 1
2 05/01/13 0
3 06/01/13 1
4 06/01/13 .
5 06/02/13 0
6 06/03/13 1
;
run;
proc sql;
create table want as
select *, put(date, monyy7.) as month_category, mean(indicator1) as rate format=fract8.
from have
where indicator1 ne .
group by put(date, monyy7.);
quit;
You data doesn't match your criteria:
1 = present ( qualify as numerator) 0=(qualify as denominator)
I think you mean, 1 or 0 is a denominator?
Is your date mmddyy or ddmmyy? Or something else?
Hello Reeza,
you are right. 0 is qualify for numerator and denominator both. and my date is SAS date;
Thank you for your correction
You can take the average of indicator1 then by month to get what you want, and use the fract8. format to get the fraction rather than a decimal.
It doesn't matter if you filter out the missing because they're not included in the average calculation anyways, but I did exclude them using a where clause.
data have;
informat date mmddyy10.;
format date date9.;
input key date mmddyy10. indicator1;
cards;
1 05/01/13 1
2 05/01/13 0
3 06/01/13 1
4 06/01/13 .
5 06/02/13 0
6 06/03/13 1
;
run;
proc sql;
create table want as
select *, put(date, monyy7.) as month_category, mean(indicator1) as rate format=fract8.
from have
where indicator1 ne .
group by put(date, monyy7.);
quit;
If I understood what you mean.
data have; input key date : mmddyy10. indicator1 ; format date mmddyy10. ; cards; 1 05/01/13 1 2 05/01/13 0 3 06/01/13 1 4 06/01/13 . 5 06/02/13 0 6 06/03/13 1 ; run; proc sql; create table want as select *,sum(indicator1=1)/count(*) as rate from have where indicator1 is not missing group by year(date),month(date); quit;
Ksharp
Message was edited by: xia keshan
Yes, you need to write by your hand,or if you are familliar with macro, you can make a macro variable to wrap them all together.
select *,sum(indicator1=1)/count(*) as rate ,
select *,sum(indicator2=1)/count(*) as rate ,
select *,sum(indicator3=1)/count(*) as rate ,
select *,sum(indicator4=1)/count(*) as rate ,
................
@ksharp awesome. I am familiar with SAS macro. I see your point. Thank you so much
By 3 month rolling do you mean:
Jan-Feb-Mar and then Feb-Mar-Apr?
As this question is answered, you should post a new question and link back to the old one if you think its relevant.
You need sub-query.
Not tested code:
proc sql;
create table want as
select * ,
( select sum(indicator1=1)/count(*)
from have
where indicator1 is not missing and date between intnx('month',-3,a.date,'s') and a.date ) as rate
from have as a;
quit;
Ksharp
Hello , and Fareeza Khurshed
Thank you for your input. . I tried input but it was unable to validate my data . Is it because there is no group by variable.
thanks
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 how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.