DATA Step, Macro, Functions and more

Aggregate Value with By Variable

Accepted Solution Solved
Reply
Occasional Contributor thb
Occasional Contributor
Posts: 7
Accepted Solution

Aggregate Value with By Variable

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.

 


SASHelp.JPG

Accepted Solutions
Solution
‎05-08-2017 01:16 PM
PROC Star
Posts: 7,356

Re: Aggregate Value with By Variable

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

View solution in original post


All Replies
Solution
‎05-08-2017 01:16 PM
PROC Star
Posts: 7,356

Re: Aggregate Value with By Variable

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

Super User
Posts: 10,460

Re: Aggregate Value with By Variable

 

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= _Smiley Happy 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.

PROC Star
Posts: 162

Re: Aggregate Value with By Variable

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

Occasional Contributor
Posts: 8

Re: Aggregate Value with By Variable

Hi

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

 

Thanks in advance

Occasional Contributor
Posts: 8

Re: Aggregate Value with By Variable

Hi 

 

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 135 views
  • 0 likes
  • 5 in conversation