## Aggregate Value with By Variable

Solved
Occasional Contributor
Posts: 7

# 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.

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

## 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

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

## 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: 13,084

## Re: Aggregate Value with By Variable

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

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.

PROC Star
Posts: 1,351

## 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

Contributor
Posts: 39

## Re: Aggregate Value with By Variable

Hi

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

Contributor
Posts: 39

## 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.