Aggregate Value with By Variable

[thb]

Hello,

I am trying to get an aggregate of my numerators and denominators for two separate time periods by Measure. Each time period has 3 months of data that I need to aggregate for each measure.

July 2016 - Sept 2016 = Q3

Oct 2016 - Dec 2016 = Q4

Please see the attachment for the data set and an example of how I need to aggregate the data.  I ran a proc summary, but it's not aggregating the way that I need it to.

Thank you.

[art297 · Accepted solution]

The proc summary part is easy. The qtr format required a bit more work:

data have;
  informat StartDate1 StartDate2 date9.;
  format StartDate1 StartDate2 date9.;
  input measure $ StartDate1 Measure2 $ StartDate2 Nu1 de1 Nu2 De2;
  cards;
A 1jul2016 A 1oct2016 3 10 4 10
A 1aug2016 A 1dec2016 5 20 2 20
B 1jul2016 B 1nov2016 1 10 5 20
B 1sep2016 B 1oct2016 1 10 4 40
;

proc fcmp outlib=work.functions.smd; 

   function qfmt(date) $;
      length yyq4 $4;
      yyq4=put(date,yyq4.);
      if substr(yyq4,3,1)='Q'
         then return(substr(yyq4,3,2));
      else return(yyq4);
   endsub;
run;

/* Specifies one or more SAS data sets that contain compiler subroutines */
/* to include during program compilation.                                */
options cmplib=(work.functions);

/* Specify the name of the function created above as the label followed by */
/* parentheses.                                                            */
proc format; 
 value qfmt other=[qfmt()]; 
run;

proc summary data=have nway;
  class Measure StartDate1;
  format StartDate1 Qfmt.;
  var Nu1 De1;
  output out=need1 (drop=_:) sum=/autoname;
run;

proc summary data=have nway;
  class Measure2 StartDate2;
  format StartDate2 Qfmt.;
  var Nu2 De2;
  output out=need2 (drop=_:) sum=/autoname;
run;

data want;
  retain measure StartDate1 Measure2 StartDate2; 
  set need1;
  set need2;
run;

Art, CEO, AnalystFinder.com

[ballardw]

First, are your dates SAS date values or not? It is very hard to tell with a picture.

If the dates are SAS date values I might start with

proc summary  data=have nway;

   class measure startdate1 measure2 startdate2;

   format startdate: yyQ. ;

   var Nu1 De1 Nu2 De2;

   output out=want (drop= _:) sum = / autoname;

run;

This will create Nu1_sum and similar names instead of SumNu1 though you can create the names with the syntax.

Actually, for most of my uses I wouldn't even rename the variables as there is only one statistic involved, but that's your choice.

I recommend keeping the Year component in the format so that you know which year the data come from. Since you show Dec 2016 data I would not be surprised to see Jan 2017 as well.

If you abosultely must have Q1, Q2 etc. Proc Format will allow you to create a custom picture format that will display dates that way.

If your dates are not SAS date variables then you need a data step that will involve something like;

data want;

    set have;

    startdate1 = cats('Q', put(input(startdate1,date9.),QTR1.));

    startdate2 = cats('Q', put(input(startdate2,date9.),QTR1.));

run;

And use the proc summary code without the format.

[novinosrin]

data have;
informat StartDate1 StartDate2 date9.;
format StartDate1 StartDate2 date9.;
input measure $ StartDate1 Measure2 $ StartDate2 Nu1 de1 Nu2 De2;
cards;
A 1jul2016 A 1oct2016 3 10 4 10
A 1aug2016 A 1dec2016 5 20 2 20
B 1jul2016 B 1nov2016 1 10 5 20
B 1sep2016 B 1oct2016 1 10 4 40
;

data temp;
set have;
_StartDate1='Q'||left(qtr(StartDate1));
_StartDate2='Q'||left(qtr(StartDate2));
rename _StartDate1= StartDate1;
rename _StartDate2= StartDate2;
drop start:;
run;

data want;
set temp;
by measure;
if first.measure then do;
SumNu1=0;
Sumde1=0;
Sumnu2=0;
Sumde2=0;
end;
SumNu1+nu1;
Sumde1+de1;
sumnu2+de2;
sumde2+de2;
if last.measure;
drop nu1 de1 nu2 de2;
run;

Regards,

Naveen Srinivasan

[soham_sas]

Hi

Can anyone suggest a proc sql solution for the same problem ?

Thanks in advance

[soham_sas]

Hi 

someone please suggest a PROC SQL solutions for this issue ,  i am asking for my knowledge purpose only .