DATA Step, Macro, Functions and more

Calculating KPIs Over Different Time Ranges

Reply
New Contributor
Posts: 3

Calculating KPIs Over Different Time Ranges

Hi all—

I am trying to calculate vales for KPIs over certain time ranges – Response Rate, Conversion Rate and Yield.  This is the desired output I’m looking for:

MTD/YTDDatesTacticResponse RateConversion RateYield Rate
Last 3 Month Performance11/1/13-1/31/14ProspectDM 0.303%27.2%0.082%
Last 3 Month Performance11/1/13-1/31/14AcquisitionDM0.486%30.1%0.146%
Last 3 Month Performance11/1/13-1/31/14ProspectBAU 0.248%25.2%0.063%
2014 YTD Performance1/1/14-1/31/14ProspectDM 0.173%25.1%0.043%
2014 YTD Performance1/1/14-1/31/14AcquisitionDM0.464%28.9%0.134%
2014 YTD Performance1/1/14-1/31/14ProspectBAU 0.123%21.4%0.026%
2013 Performance1/1/13-12/31/13ProspectDM 0.412%25.7%0.106%
2013 Performance1/1/13-12/31/13AcquisitionDM0.648%28.4%0.184%
2013 Performance1/1/13-12/31/13ProspectBAU 0.324%23.6%0.076%

I have this program which calculates the KPIs:

data have;

set want;

Response_Rate=round((Calls/Mailed),0.00001) ;

Conversion_Rate=round((Sales / Calls),0.00001);

Yield=round((Sales /Mailed),0.00001);

Label

Response_Rate='Response Rate'

Conversion_Rate='Conversion Rate'

Yield='Yield Rate';

format Response_Rate Conversion_Rate Yield Percent10.2;

run;


What I’m having trouble with is how to calculate them for the time ranges of 1. Last 3 Month Performance, 2.Year to Date Performance, and 3.the previous year’s performance by tactic. I also need a Date range variable which concatenates the date range.  I am familiar with the intnx function:

drop_date between &start and intnx('month',&start,-3,"Sameday")

drop_date between &start and intnx('year',&start,-1,"Sameday");

Below is some sample data. Any assistance will be greatly appreciated. Thanks!

data have;

length Insertion 8  tactic $13;

INPUT Insertion :anydtdte9. tactic $ Mailed  Calls  Sales ;

format Insertion  date9.;

datalines;

3/2/2013 ProspectDM 167 53 19

4/6/2013 ProspectDM 216 65 12

2/23/2013 ProspectDM 153 73 26

5/11/2013 ProspectDM 207 64 4

5/4/2013 ProspectDM 209 69 15

6/22/2013 ProspectDM 123 69 8

1/26/2013 ProspectDM 109 59 22

2/9/2013 ProspectDM 121 56 1

2/2/2013 ProspectDM 182 63 21

1/19/2013 ProspectDM 151 57 11

6/8/2013 ProspectDM 171 57 27

6/15/2013 ProspectDM 170 68 14

4/20/2013 ProspectDM 167 50 17

3/9/2013 ProspectDM 168 66 17

5/18/2013 ProspectDM 141 50 28

8/17/2013 ProspectDM 217 67 13

9/21/2013 ProspectDM 193 63 3

5/25/2013 ProspectDM 161 57 22

1/4/2014 ProspectDM 182 51 18

9/14/2013 ProspectDM 124 72 16

6/1/2013 ProspectDM 118 52 20

6/29/2013 ProspectDM 115 69 22

8/31/2013 ProspectDM 146 67 16

8/3/2013 ProspectDM 157 67 3

1/11/2014 ProspectDM 183 62 3

1/18/2014 ProspectDM 206 74 8

9/7/2013 ProspectDM 168 74 28

12/28/2013 ProspectDM 201 71 16

10/12/2013 ProspectDM 133 59 24

2/1/2014 AcquisitionDM 176 50 23

1/25/2014 AcquisitionDM 136 68 20

2/16/2013 AcquisitionDM 122 66 10

8/24/2013 AcquisitionDM 190 50 22

9/28/2013 AcquisitionDM 210 63 8

12/21/2013 AcquisitionDM 111 58 8

3/30/2013 AcquisitionDM 156 70 19

4/13/2013 AcquisitionDM 149 66 28

5/11/2013 AcquisitionDM 149 60 20

5/18/2013 AcquisitionDM 164 63 7

6/22/2013 AcquisitionDM 151 60 20

3/16/2013 AcquisitionDM 182 66 22

3/9/2013 AcquisitionDM 106 61 19

4/27/2013 AcquisitionDM 140 72 29

6/1/2013 AcquisitionDM 122 55 11

5/25/2013 AcquisitionDM 109 54 19

1/4/2014 AcquisitionDM 113 65 11

11/16/2013 AcquisitionDM 160 57 30

12/7/2013 AcquisitionDM 114 64 17

12/14/2013 AcquisitionDM 98 61 26

1/18/2014 AcquisitionDM 147 71 9

4/6/2013 AcquisitionDM 152 52 29

12/1/2013 AcquisitionDM 215 72 14

12/15/2013 AcquisitionDM 209 61 25

1/12/2013 AcquisitionDM 175 75 21

2/23/2013 AcquisitionDM 101 66 8

3/30/2013 AcquisitionDM 153 57 4

1/5/2013 AcquisitionDM 180 74 29

11/24/2013 AcquisitionDM 156 50 20

5/11/2013 AcquisitionDM 122 68 17

12/29/2013 AcquisitionDM 198 70 20

4/13/2013 AcquisitionDM 111 55 21

5/4/2013 AcquisitionDM 97 56 6

4/27/2013 AcquisitionDM 124 66 19

6/15/2013 AcquisitionDM 174 67 27

12/22/2013 AcquisitionDM 142 58 22

6/1/2013 AcquisitionDM 183 65 4

7/20/2013 AcquisitionDM 181 66 4

3/9/2013 AcquisitionDM 144 68 15

5/4/2013 AcquisitionDM 97 59 18

7/27/2013 AcquisitionDM 199 54 5

4/13/2013 AcquisitionDM 118 56 1

8/24/2013 AcquisitionDM 153 51 2

3/16/2013 AcquisitionDM 136 67 13

8/31/2013 AcquisitionDM 96 57 19

4/27/2013 AcquisitionDM 184 66 15

6/29/2013 AcquisitionDM 119 55 3

5/18/2013 AcquisitionDM 111 74 5

4/20/2013 AcquisitionDM 192 72 15

4/20/2013 AcquisitionDM 153 69 30

9/28/2013 AcquisitionDM 164 50 26

9/28/2013 AcquisitionDM 192 55 21

8/3/2013 AcquisitionDM 163 51 11

12/7/2013 AcquisitionDM 209 58 15

6/22/2013 AcquisitionDM 101 55 3

10/26/2013 AcquisitionDM 171 74 11

7/6/2013 AcquisitionDM 172 61 3

11/2/2013 AcquisitionDM 153 68 6

5/25/2013 AcquisitionDM 124 55 2

9/7/2013 AcquisitionDM 144 66 20

8/10/2013 AcquisitionDM 135 69 9

11/30/2013 AcquisitionDM 181 57 18

10/5/2013 AcquisitionDM 104 60 13

10/19/2013 AcquisitionDM 115 50 16

11/23/2013 AcquisitionDM 209 53 30

1/25/2014 AcquisitionDM 137 63 22

8/24/2013 AcquisitionDM 141 50 23

8/17/2013 AcquisitionDM 152 64 8

9/14/2013 AcquisitionDM 106 61 7

11/16/2013 AcquisitionDM 169 60 11

10/12/2013 AcquisitionDM 112 65 15

9/21/2013 AcquisitionDM 173 73 26

10/5/2013 AcquisitionDM 175 53 1

12/21/2013 AcquisitionDM 104 68 28

2/1/2014 AcquisitionDM 113 62 30

7/14/2013 AcquisitionDM 114 73 28

7/28/2013 AcquisitionDM 193 59 26

9/22/2013 AcquisitionDM 187 55 15

7/14/2013 AcquisitionDM 214 58 13

8/11/2013 AcquisitionDM 151 52 12

12/1/2013 AcquisitionDM 106 60 21

12/15/2014 AcquisitionDM 105 55 27

8/18/2013 AcquisitionDM 101 67 24

1/19/2013 AcquisitionDM 185 55 1

1/26/2013 AcquisitionDM 208 60 24

3/9/2013 AcquisitionDM 129 65 14

9/29/2013 AcquisitionDM 185 66 2

12/8/2013 AcquisitionDM 149 68 10

3/2/2013 AcquisitionDM 192 58 3

2/23/2013 AcquisitionDM 146 72 21

12/29/2013 AcquisitionDM 209 53 28

4/13/2013 AcquisitionDM 213 56 20

12/1/2013 AcquisitionDM 140 64 15

2/2/2013 AcquisitionDM 128 75 3

3/30/2013 AcquisitionDM 135 68 12

5/18/2013 AcquisitionDM 140 52 20

12/22/2013 AcquisitionDM 206 66 15

1/5/2013 AcquisitionDM 119 63 20

6/1/2013 AcquisitionDM 126 73 2

2/9/2013 ProspectBAU 175 59 12

6/8/2013 ProspectBAU 190 51 12

4/27/2013 ProspectBAU 107 54 19

7/13/2013 ProspectBAU 137 54 27

2/16/2013 ProspectBAU 192 68 8

3/16/2013 ProspectBAU 101 63 3

5/18/2013 ProspectBAU 156 60 23

6/1/2013 ProspectBAU 142 50 20

9/28/2013 ProspectBAU 168 75 21

4/20/2013 ProspectBAU 187 66 30

5/4/2013 ProspectBAU 126 74 4

3/23/2013 ProspectBAU 152 57 23

8/31/2013 ProspectBAU 108 53 14

11/2/2013 ProspectBAU 154 67 26

5/25/2013 ProspectBAU 201 64 22

10/5/2013 ProspectBAU 107 54 8

6/22/2013 ProspectBAU 204 70 22

8/10/2013 ProspectBAU 128 55 19

9/14/2013 ProspectBAU 130 64 24

6/29/2013 ProspectBAU 147 61 29

12/14/2013 ProspectBAU 97 54 5

1/18/2014 ProspectBAU 97 75 4

12/7/2013 ProspectBAU 158 65 8

8/3/2013 ProspectBAU 178 67 16

1/11/2014 ProspectBAU 97 65 27

9/21/2013 ProspectBAU 147 55 28

10/12/2013 ProspectBAU 126 62 1

2/1/2014 ProspectBAU 158 63 2

7/21/2012 ProspectBAU 95 61 1

11/17/2013 ProspectBAU 157 64 11

12/8/2013 ProspectBAU 133 63 23

1/12/2013 ProspectBAU 167 54 6

4/6/2013 ProspectBAU 172 56 6

7/20/2013 ProspectBAU 110 57 30

8/24/2013 ProspectBAU 174 73 11

9/7/2013 ProspectBAU 157 70 14

1/25/2014 ProspectBAU 178 63 29

1/25/2014 ProspectBAU 178 63 29

;

Ask a Question
Discussion stats
  • 0 replies
  • 190 views
  • 0 likes
  • 1 in conversation