topic Re: sum by group variable and merge it back to original dataset in SAS Programming

sum by group variable and merge it back to original dataset

learner_sas — Wed, 03 Jul 2013 15:06:55 GMT

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

Re: sum by group variable and merge it back to original dataset

Reeza — Wed, 03 Jul 2013 15:21:30 GMT

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?

Re: sum by group variable and merge it back to original dataset

learner_sas — Wed, 03 Jul 2013 15:23:54 GMT

Hello Reeza,

you are right. 0 is qualify for numerator and denominator both. and my date is SAS date;

Thank you for your correction

Re: sum by group variable and merge it back to original dataset

Reeza — Wed, 03 Jul 2013 15:28:42 GMT

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;

Re: sum by group variable and merge it back to original dataset

Ksharp — Wed, 03 Jul 2013 15:43:00 GMT

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

Re: sum by group variable and merge it back to original dataset

learner_sas — Wed, 03 Jul 2013 15:50:50 GMT

thank you @Reeza and . Both of these works.One more concern. If I have more than indicators do I have to create mean statement for 10 different indicators. Is there a shortcut or I have to create rate_1 ,rate_2 ....rate_10 for given indicator.

Thank you again

Re: sum by group variable and merge it back to original dataset

Ksharp — Wed, 03 Jul 2013 15:58:31 GMT

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 ,

................

Re: sum by group variable and merge it back to original dataset

learner_sas — Wed, 03 Jul 2013 16:02:36 GMT

@ksharp awesome. I am familiar with SAS macro. I see your point. Thank you so much

Re: sum by group variable and merge it back to original dataset

learner_sas — Tue, 09 Jul 2013 21:29:49 GMT

Hello I did as of your suggestion and I got my output.Now I am going more deeper. I want to find 3 month rolling sum same as monthly summary.Is there a way to do that.

Re: sum by group variable and merge it back to original dataset

Reeza — Tue, 09 Jul 2013 21:34:52 GMT

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.

Re: sum by group variable and merge it back to original dataset

learner_sas — Tue, 09 Jul 2013 23:29:18 GMT

yes. I am hoping to run that way

Re: sum by group variable and merge it back to original dataset

Ksharp — Wed, 10 Jul 2013 02:14:52 GMT

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

Re: sum by group variable and merge it back to original dataset

learner_sas — Wed, 10 Jul 2013 14:00:33 GMT

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