Solved
New Contributor
Posts: 3

create a new variable by taking the sum of another variable by group

[ Edited ]

Beginning SAS programmer any help is appreciated. I am trying to calculate an approval rate (%) from the variable "Decision" in sas instead of doing the calculation in excel. I am having a problem figuring out where to start.

The data is in this format

Decision=Approved

Decision=Decline

Decision=Withdraw

I would like to know how to sum all the Decision=Approved in a given month (same for Decline and Withdraw). And then compute an Approval Rate by taking the (sum of Approved) divided by the (sum of Approved + sum of Decline) to get a %. For example

if sum of approved is 50 and sum of decline is 25 the approval rate would be 50/(50+25)=.6666 and I want it to show that the approval rate is 66% in the proc tabulate.

Below is the macro I wrote that runs correctly to give me the results I need for each month, but not sure how to calculate the Approval Rate for Each MMYY and then have it show in the proc tabulate.

%macro FUN(yymm_st, yymm_end);

%let yymm_temp= &yymm_st.;

%if %sysfunc (exist(FULL)) %then

%do;

proc delete data=FULL;

run;

%end;

%do %while (&yymm_temp. <= &yymm_end.);

%if %sysfunc (exist(MRM&yymm_temp.)) %then

%do;

data TEST&yymm_temp. (where=(REQUEST=1));

format DATE monyy.;

DATE = (dtrequest);

set FULL&yymm_temp.;

if DECISION='Approve'

or DECISION='Partial' then

DECISION='Approved';

run;

proc append base=FLL

data=TEST&yymm_temp. FORCE;

run;

proc delete data=TEST&yymm_temp.;

run;

%end;

%if %substr (&yymm_temp,3,2)=12 %then

%let yymm_temp=%eval(&yymm_temp +89);

%else %let yymm_temp=%eval(&yymm_temp +1);

%end;

%mend lookup_loop;

%FUN (1701, 1712);

Proc tabulate data=FULL;

Class Criteria DECISION DATE;

keylabel all='total';

Table Criteria*(DECISION all), DATE;

run;

Accepted Solutions
Solution
‎04-26-2018 05:23 PM
Super User
Posts: 24,028

Re: create a new variable by taking the sum of another variable by group

Here's two ways you can do it. The PROC FREQ will generate results in the table and then the data is actually in the WANT data set, but I've filtered it for only approval rates.

``````data have;
infile cards truncover;
informat date anydtdte.;
format date monyy7.;
input
DATE Record REQUEST \$ CRITERIA \$  DECISION \$;
cards;
Jan-17 1 Transfer a Decline
Jan-17 2 Transfer i Decline
Jan-17 3 Transfer o Approved
Jan-17 4 Transfer a Approved
Jan-17 5 Transfer a Approved
Jan-17 6 Transfer a Approved
Jan-17 7 Transfer a Withdraw
Jan-17 8 Transfer a Decline
Jan-17 9 Transfer a Decline
Jan-17 10 Transfer b Approved
Jan-17 11 Transfer d Withdraw
Jan-17 12 Transfer d Approved
Jan-17 13 Transfer d Decline
Jan-17 14 Transfer d Withdraw
Jan-17 15 Transfer f Approved
Jan-17 16 Transfer f Decline
Jan-17 17 Transfer g Approved
Jan-17 18 Transfer g Decline
Jan-17 19 Transfer g Approved
Jan-17 20 Transfer g Decline
Jan-17 21 Transfer h Decline
Jan-17 22 Transfer h Decline
Jan-17 23 Transfer i Decline
Jan-17 24 Transfer i Decline
Jan-17 25 Transfer k Decline
Jan-17 26 Transfer m Decline
Jan-17 27 Transfer m Withdraw
Jan-17 28 Transfer o Decline
Jan-17 29 Transfer p Approved
Jan-17 30 Transfer p Decline
Feb-17 31 Transfer a Withdraw
Feb-17 32 Transfer a Approved
Feb-17 33 Transfer a Decline
Feb-17 34 Transfer b Withdraw
Feb-17 35 Transfer b Approved
Feb-17 36 Transfer c Approved
Feb-17 37 Transfer c Decline
Feb-17 38 Transfer c Approved
Feb-17 39 Transfer c Decline
Feb-17 40 Transfer d Approved
Feb-17 41 Transfer d Approved
Feb-17 42 Transfer d Approved
Feb-17 43 Transfer e Decline
Feb-17 44 Transfer e Decline
Feb-17 45 Transfer e Withdraw
Feb-17 46 Transfer g Approved
Feb-17 47 Transfer g Approved
Feb-17 48 Transfer h Withdraw
Feb-17 49 Transfer h Approved
Feb-17 50 Transfer i Decline
Feb-17 51 Transfer i Approved
Feb-17 52 Transfer i Decline
Feb-17 53 Transfer k Approved
Feb-17 54 Transfer k Approved
Feb-17 55 Transfer m Withdraw
Feb-17 56 Transfer m Decline
Feb-17 57 Transfer n Decline
Feb-17 58 Transfer p Approved
Feb-17 59 Transfer p Approved
Feb-17 60 Transfer p Withdraw
Mar-17 61 Transfer a Withdraw
Mar-17 62 Transfer b Approved
Mar-17 63 Transfer b Withdraw
Mar-17 64 Transfer c Decline
Mar-17 65 Transfer d Approved
Mar-17 66 Transfer d Decline
Mar-17 67 Transfer d Approved
Mar-17 68 Transfer e Approved
Mar-17 69 Transfer e Decline
Mar-17 70 Transfer e Withdraw
Mar-17 71 Transfer e Decline
Mar-17 72 Transfer e Decline
Mar-17 73 Transfer f Approved
Mar-17 74 Transfer f Decline
Mar-17 75 Transfer f Approved
Mar-17 76 Transfer f Approved
Mar-17 77 Transfer g Approved
Mar-17 78 Transfer g Approved
Mar-17 79 Transfer h Approved
Mar-17 80 Transfer h Approved
Mar-17 81 Transfer i Decline
Mar-17 82 Transfer i Decline
Mar-17 83 Transfer i Approved
Mar-17 84 Transfer k Approved
Mar-17 85 Transfer m Decline
Mar-17 86 Transfer o Decline
Mar-17 87 Transfer p Approved
Mar-17 88 Transfer p Approved
Mar-17 89 Transfer p Decline
Mar-17 90 Transfer p Decline
Apr-17 91 Transfer a Decline
Apr-17 92 Transfer d Withdraw
Apr-17 93 Transfer d Approved
Apr-17 94 Transfer d Approved
Apr-17 95 Transfer d Approved
Apr-17 96 Transfer d Approved
Apr-17 97 Transfer e Decline
Apr-17 98 Transfer e Withdraw
Apr-17 99 Transfer e Withdraw
Apr-17 100 Transfer e Withdraw
Apr-17 101 Transfer f Decline
Apr-17 102 Transfer f Approved
Apr-17 103 Transfer g Decline
Apr-17 104 Transfer g Decline
Apr-17 105 Transfer g Approved
Apr-17 106 Transfer h Withdraw
Apr-17 107 Transfer h Approved
Apr-17 108 Transfer h Decline
Apr-17 109 Transfer i Withdraw
Apr-17 110 Transfer i Approved
Apr-17 111 Transfer i Decline
Apr-17 112 Transfer k Approved
Apr-17 113 Transfer n Withdraw
Apr-17 114 Transfer o Approved
Apr-17 115 Transfer o Approved
Apr-17 116 Transfer p Decline
Apr-17 117 Transfer p Decline
Apr-17 118 Transfer p Decline
Apr-17 119 Transfer p Approved
Apr-17 120 Transfer p Withdraw
May-17 121 Transfer a Withdraw
May-17 122 Transfer a Approved
May-17 123 Transfer a Decline
May-17 124 Transfer a Decline
May-17 125 Transfer b Decline
May-17 126 Transfer c Approved
May-17 127 Transfer c Decline
May-17 128 Transfer d Approved
May-17 129 Transfer d Approved
May-17 130 Transfer e Decline
May-17 131 Transfer e Decline
May-17 132 Transfer h Approved
May-17 133 Transfer h Approved
May-17 134 Transfer h Approved
May-17 135 Transfer i Decline
May-17 136 Transfer i Approved
May-17 137 Transfer k Approved
May-17 138 Transfer k Approved
May-17 139 Transfer k Approved
May-17 140 Transfer k Withdraw
May-17 141 Transfer m Decline
May-17 142 Transfer n Decline
May-17 143 Transfer o Approved
May-17 144 Transfer p Decline
May-17 145 Transfer p Approved
May-17 146 Transfer p Decline
May-17 147 Transfer p Approved
May-17 148 Transfer p Decline
May-17 149 Transfer p Withdraw
May-17 150 Transfer p Decline
Jun-17 151 Transfer a Approved
Jun-17 152 Transfer c Approved
Jun-17 153 Transfer d Decline
Jun-17 154 Transfer d Approved
Jun-17 155 Transfer k Approved
Jun-17 156 Transfer l Withdraw
Jun-17 157 Transfer l Decline
Jun-17 158 Transfer c Decline
Jun-17 159 Transfer e Decline
Jun-17 160 Transfer g Approved
Jun-17 161 Transfer g Decline
Jun-17 162 Transfer h Approved
Jun-17 163 Transfer k Approved
Jun-17 164 Transfer k Approved
Jun-17 165 Transfer m Decline
Jun-17 166 Transfer p Decline
Jun-17 167 Transfer c Withdraw
Jun-17 168 Transfer c Withdraw
Jun-17 169 Transfer k Approved
Jun-17 170 Transfer k Approved
Jun-17 171 Transfer m Decline
Jun-17 172 Transfer o Decline
Jun-17 173 Transfer o Decline
Jun-17 174 Transfer p Decline
Jun-17 175 Transfer p Withdraw
Jun-17 176 Transfer p Approved
Jun-17 177 Transfer p Decline
Jun-17 178 Transfer p Approved
Jun-17 179 Transfer p Approved
Jun-17 180 Transfer p Approved
Jul-17 181 Transfer a Approved
Jul-17 182 Transfer a Decline
Jul-17 183 Transfer a Withdraw
Jul-17 184 Transfer c Withdraw
Jul-17 185 Transfer c Approved
Jul-17 186 Transfer c Withdraw
Jul-17 187 Transfer d Withdraw
Jul-17 188 Transfer d Approved
Jul-17 189 Transfer e Decline
Jul-17 190 Transfer e Decline
Jul-17 191 Transfer e Withdraw
Jul-17 192 Transfer e Withdraw
Jul-17 193 Transfer e Decline
Jul-17 194 Transfer e Withdraw
Jul-17 195 Transfer f Decline
Jul-17 196 Transfer f Withdraw
Jul-17 197 Transfer f Decline
Jul-17 198 Transfer f Decline
Jul-17 199 Transfer h Approved
Jul-17 200 Transfer h Withdraw
Jul-17 201 Transfer h Approved
Jul-17 202 Transfer i Approved
Jul-17 203 Transfer k Approved
Jul-17 204 Transfer m Decline
Jul-17 205 Transfer n Approved
Jul-17 206 Transfer o Approved
Jul-17 207 Transfer p Decline
Jul-17 208 Transfer p Decline
Jul-17 209 Transfer p Decline
Jul-17 210 Transfer p Decline
Aug-17 211 Transfer c Decline
Aug-17 212 Transfer c Approved
Aug-17 213 Transfer d Withdraw
Aug-17 214 Transfer d Approved
Aug-17 215 Transfer d Approved
Aug-17 216 Transfer d Withdraw
Aug-17 217 Transfer e Decline
Aug-17 218 Transfer e Withdraw
Aug-17 219 Transfer e Decline
Aug-17 220 Transfer e Decline
Aug-17 221 Transfer e Decline
Aug-17 222 Transfer e Decline
Aug-17 223 Transfer e Withdraw
Aug-17 224 Transfer e Decline
Aug-17 225 Transfer g Approved
Aug-17 226 Transfer g Approved
Aug-17 227 Transfer g Approved
Aug-17 228 Transfer h Withdraw
Aug-17 229 Transfer h Approved
Aug-17 230 Transfer h Approved
Aug-17 231 Transfer i Withdraw
Aug-17 232 Transfer i Decline
Aug-17 233 Transfer i Decline
Aug-17 234 Transfer j Approved
Aug-17 235 Transfer k Approved
Aug-17 236 Transfer k Approved
Aug-17 237 Transfer m Decline
Aug-17 238 Transfer o Approved
Aug-17 239 Transfer p Decline
Aug-17 240 Transfer p Approved
Sep-17 241 Transfer a Withdraw
Sep-17 242 Transfer c Withdraw
Sep-17 243 Transfer d Withdraw
Sep-17 244 Transfer d Withdraw
Sep-17 245 Transfer d Decline
Sep-17 246 Transfer e Withdraw
Sep-17 247 Transfer e Approved
Sep-17 248 Transfer e Withdraw
Sep-17 249 Transfer e Withdraw
Sep-17 250 Transfer e Decline
Sep-17 251 Transfer e Withdraw
Sep-17 252 Transfer f Approved
Sep-17 253 Transfer f Approved
Sep-17 254 Transfer g Decline
Sep-17 255 Transfer g Approved
Sep-17 256 Transfer h Approved
Sep-17 257 Transfer i Approved
Sep-17 258 Transfer k Approved
Sep-17 259 Transfer k Decline
Sep-17 260 Transfer m Decline
Sep-17 261 Transfer n Decline
Sep-17 262 Transfer n Decline
Sep-17 263 Transfer n Decline
Sep-17 264 Transfer o Approved
Sep-17 265 Transfer o Approved
Sep-17 266 Transfer p Approved
Sep-17 267 Transfer p Decline
Sep-17 268 Transfer p Approved
Sep-17 269 Transfer p Approved
Sep-17 270 Transfer p Decline
Oct-17 271 Transfer a Withdraw
Oct-17 272 Transfer b Withdraw
Oct-17 273 Transfer d Approved
Oct-17 274 Transfer d Withdraw
Oct-17 275 Transfer e Decline
Oct-17 276 Transfer e Withdraw
Oct-17 277 Transfer e Decline
Oct-17 278 Transfer e Decline
Oct-17 279 Transfer f Approved
Oct-17 280 Transfer h Approved
Oct-17 281 Transfer h Approved
Oct-17 282 Transfer h Withdraw
Oct-17 283 Transfer h Withdraw
Oct-17 284 Transfer h Approved
Oct-17 285 Transfer i Withdraw
Oct-17 286 Transfer i Approved
Oct-17 287 Transfer i Decline
Oct-17 288 Transfer i Withdraw
Oct-17 289 Transfer i Approved
Oct-17 290 Transfer i Approved
Oct-17 291 Transfer i Approved
Oct-17 292 Transfer j Decline
Oct-17 293 Transfer k Approved
Oct-17 294 Transfer k Approved
Oct-17 295 Transfer m Approved
Oct-17 296 Transfer o Withdraw
Oct-17 297 Transfer p Approved
Oct-17 298 Transfer p Decline
Oct-17 299 Transfer p Approved
Oct-17 300 Transfer p Decline
Nov-17 301 Transfer c Decline
Nov-17 302 Transfer c Approved
Nov-17 303 Transfer c Withdraw
Nov-17 304 Transfer c Approved
Nov-17 305 Transfer d Approved
Nov-17 306 Transfer d Decline
Nov-17 307 Transfer e Withdraw
Nov-17 308 Transfer e Decline
Nov-17 309 Transfer e Decline
Nov-17 310 Transfer f Approved
Nov-17 311 Transfer g Approved
Nov-17 312 Transfer g Decline
Nov-17 313 Transfer g Approved
Nov-17 314 Transfer i Approved
Nov-17 315 Transfer i Approved
Nov-17 316 Transfer i Approved
Nov-17 317 Transfer k Approved
Nov-17 318 Transfer k Approved
Nov-17 319 Transfer k Approved
Nov-17 320 Transfer m Decline
Nov-17 321 Transfer n Withdraw
Nov-17 322 Transfer o Approved
Nov-17 323 Transfer o Approved
Nov-17 324 Transfer o Withdraw
Nov-17 325 Transfer o Approved
Nov-17 326 Transfer p Withdraw
Nov-17 327 Transfer p Approved
Nov-17 328 Transfer p Decline
Nov-17 329 Transfer p Decline
Nov-17 330 Transfer p Decline
Dec-17 331 Transfer d Approved
Dec-17 332 Transfer e Decline
Dec-17 333 Transfer f Decline
Dec-17 334 Transfer p Withdraw
Dec-17 335 Transfer a Approved
Dec-17 336 Transfer a Withdraw
Dec-17 337 Transfer a Decline
Dec-17 338 Transfer b Approved
Dec-17 339 Transfer d Approved
Dec-17 340 Transfer d Decline
Dec-17 341 Transfer d Approved
Dec-17 342 Transfer e Decline
Dec-17 343 Transfer e Withdraw
Dec-17 344 Transfer f Withdraw
Dec-17 345 Transfer f Decline
Dec-17 346 Transfer h Decline
Dec-17 347 Transfer i Approved
Dec-17 348 Transfer i Withdraw
Dec-17 349 Transfer i Approved
Dec-17 350 Transfer i Approved
Dec-17 351 Transfer k Withdraw
Dec-17 352 Transfer k Approved
Dec-17 353 Transfer k Approved
Dec-17 354 Transfer k Approved
Dec-17 355 Transfer l Withdraw
Dec-17 356 Transfer l Decline
Dec-17 357 Transfer n Decline
Dec-17 358 Transfer p Decline
Dec-17 359 Transfer p Approved
Dec-17 360 Transfer p Decline
;;;;
run;

proc freq data=have ;
by date;
format date monyy7.;
table decision / out=want (where = (decision='Approved')) outpct list;

where decision in ('Approved', 'Decline');
run;

title 'summary of results';
proc print data=want;
run;``````

All Replies
Super User
Posts: 24,028

Re: create a new variable by taking the sum of another variable by group

The best method would likely be to add a BY statement, or another CLASS variable.

If you could provide some sample data we could offer a better suggestion.

If you have decision with just those two categories you could also just use PROC FREQ. Depending on the data aggregations you may be able to use FORMATS to simplify this.

``````proc freq data=have;
by date;
table decision / out = want;;
run;``````

If you can add some sample data I can probably test a full solution but hopefully this is helpful.

New Contributor
Posts: 3

Re: create a new variable by taking the sum of another variable by group

Hi Reeza thank you for the offer of help. I tried to attach a excel file with sample data but it didn't work so I copied it here will this work?

DATE Record REQUEST CRITERIA DECISION
Jan-17 1 Transfer a Decline
Jan-17 2 Transfer i Decline
Jan-17 3 Transfer o Approved
Jan-17 4 Transfer a Approved
Jan-17 5 Transfer a Approved
Jan-17 6 Transfer a Approved
Jan-17 7 Transfer a Withdraw
Jan-17 8 Transfer a Decline
Jan-17 9 Transfer a Decline
Jan-17 10 Transfer b Approved
Jan-17 11 Transfer d Withdraw
Jan-17 12 Transfer d Approved
Jan-17 13 Transfer d Decline
Jan-17 14 Transfer d Withdraw
Jan-17 15 Transfer f Approved
Jan-17 16 Transfer f Decline
Jan-17 17 Transfer g Approved
Jan-17 18 Transfer g Decline
Jan-17 19 Transfer g Approved
Jan-17 20 Transfer g Decline
Jan-17 21 Transfer h Decline
Jan-17 22 Transfer h Decline
Jan-17 23 Transfer i Decline
Jan-17 24 Transfer i Decline
Jan-17 25 Transfer k Decline
Jan-17 26 Transfer m Decline
Jan-17 27 Transfer m Withdraw
Jan-17 28 Transfer o Decline
Jan-17 29 Transfer p Approved
Jan-17 30 Transfer p Decline
Feb-17 31 Transfer a Withdraw
Feb-17 32 Transfer a Approved
Feb-17 33 Transfer a Decline
Feb-17 34 Transfer b Withdraw
Feb-17 35 Transfer b Approved
Feb-17 36 Transfer c Approved
Feb-17 37 Transfer c Decline
Feb-17 38 Transfer c Approved
Feb-17 39 Transfer c Decline
Feb-17 40 Transfer d Approved
Feb-17 41 Transfer d Approved
Feb-17 42 Transfer d Approved
Feb-17 43 Transfer e Decline
Feb-17 44 Transfer e Decline
Feb-17 45 Transfer e Withdraw
Feb-17 46 Transfer g Approved
Feb-17 47 Transfer g Approved
Feb-17 48 Transfer h Withdraw
Feb-17 49 Transfer h Approved
Feb-17 50 Transfer i Decline
Feb-17 51 Transfer i Approved
Feb-17 52 Transfer i Decline
Feb-17 53 Transfer k Approved
Feb-17 54 Transfer k Approved
Feb-17 55 Transfer m Withdraw
Feb-17 56 Transfer m Decline
Feb-17 57 Transfer n Decline
Feb-17 58 Transfer p Approved
Feb-17 59 Transfer p Approved
Feb-17 60 Transfer p Withdraw
Mar-17 61 Transfer a Withdraw
Mar-17 62 Transfer b Approved
Mar-17 63 Transfer b Withdraw
Mar-17 64 Transfer c Decline
Mar-17 65 Transfer d Approved
Mar-17 66 Transfer d Decline
Mar-17 67 Transfer d Approved
Mar-17 68 Transfer e Approved
Mar-17 69 Transfer e Decline
Mar-17 70 Transfer e Withdraw
Mar-17 71 Transfer e Decline
Mar-17 72 Transfer e Decline
Mar-17 73 Transfer f Approved
Mar-17 74 Transfer f Decline
Mar-17 75 Transfer f Approved
Mar-17 76 Transfer f Approved
Mar-17 77 Transfer g Approved
Mar-17 78 Transfer g Approved
Mar-17 79 Transfer h Approved
Mar-17 80 Transfer h Approved
Mar-17 81 Transfer i Decline
Mar-17 82 Transfer i Decline
Mar-17 83 Transfer i Approved
Mar-17 84 Transfer k Approved
Mar-17 85 Transfer m Decline
Mar-17 86 Transfer o Decline
Mar-17 87 Transfer p Approved
Mar-17 88 Transfer p Approved
Mar-17 89 Transfer p Decline
Mar-17 90 Transfer p Decline
Apr-17 91 Transfer a Decline
Apr-17 92 Transfer d Withdraw
Apr-17 93 Transfer d Approved
Apr-17 94 Transfer d Approved
Apr-17 95 Transfer d Approved
Apr-17 96 Transfer d Approved
Apr-17 97 Transfer e Decline
Apr-17 98 Transfer e Withdraw
Apr-17 99 Transfer e Withdraw
Apr-17 100 Transfer e Withdraw
Apr-17 101 Transfer f Decline
Apr-17 102 Transfer f Approved
Apr-17 103 Transfer g Decline
Apr-17 104 Transfer g Decline
Apr-17 105 Transfer g Approved
Apr-17 106 Transfer h Withdraw
Apr-17 107 Transfer h Approved
Apr-17 108 Transfer h Decline
Apr-17 109 Transfer i Withdraw
Apr-17 110 Transfer i Approved
Apr-17 111 Transfer i Decline
Apr-17 112 Transfer k Approved
Apr-17 113 Transfer n Withdraw
Apr-17 114 Transfer o Approved
Apr-17 115 Transfer o Approved
Apr-17 116 Transfer p Decline
Apr-17 117 Transfer p Decline
Apr-17 118 Transfer p Decline
Apr-17 119 Transfer p Approved
Apr-17 120 Transfer p Withdraw
May-17 121 Transfer a Withdraw
May-17 122 Transfer a Approved
May-17 123 Transfer a Decline
May-17 124 Transfer a Decline
May-17 125 Transfer b Decline
May-17 126 Transfer c Approved
May-17 127 Transfer c Decline
May-17 128 Transfer d Approved
May-17 129 Transfer d Approved
May-17 130 Transfer e Decline
May-17 131 Transfer e Decline
May-17 132 Transfer h Approved
May-17 133 Transfer h Approved
May-17 134 Transfer h Approved
May-17 135 Transfer i Decline
May-17 136 Transfer i Approved
May-17 137 Transfer k Approved
May-17 138 Transfer k Approved
May-17 139 Transfer k Approved
May-17 140 Transfer k Withdraw
May-17 141 Transfer m Decline
May-17 142 Transfer n Decline
May-17 143 Transfer o Approved
May-17 144 Transfer p Decline
May-17 145 Transfer p Approved
May-17 146 Transfer p Decline
May-17 147 Transfer p Approved
May-17 148 Transfer p Decline
May-17 149 Transfer p Withdraw
May-17 150 Transfer p Decline
Jun-17 151 Transfer a Approved
Jun-17 152 Transfer c Approved
Jun-17 153 Transfer d Decline
Jun-17 154 Transfer d Approved
Jun-17 155 Transfer k Approved
Jun-17 156 Transfer l Withdraw
Jun-17 157 Transfer l Decline
Jun-17 158 Transfer c Decline
Jun-17 159 Transfer e Decline
Jun-17 160 Transfer g Approved
Jun-17 161 Transfer g Decline
Jun-17 162 Transfer h Approved
Jun-17 163 Transfer k Approved
Jun-17 164 Transfer k Approved
Jun-17 165 Transfer m Decline
Jun-17 166 Transfer p Decline
Jun-17 167 Transfer c Withdraw
Jun-17 168 Transfer c Withdraw
Jun-17 169 Transfer k Approved
Jun-17 170 Transfer k Approved
Jun-17 171 Transfer m Decline
Jun-17 172 Transfer o Decline
Jun-17 173 Transfer o Decline
Jun-17 174 Transfer p Decline
Jun-17 175 Transfer p Withdraw
Jun-17 176 Transfer p Approved
Jun-17 177 Transfer p Decline
Jun-17 178 Transfer p Approved
Jun-17 179 Transfer p Approved
Jun-17 180 Transfer p Approved
Jul-17 181 Transfer a Approved
Jul-17 182 Transfer a Decline
Jul-17 183 Transfer a Withdraw
Jul-17 184 Transfer c Withdraw
Jul-17 185 Transfer c Approved
Jul-17 186 Transfer c Withdraw
Jul-17 187 Transfer d Withdraw
Jul-17 188 Transfer d Approved
Jul-17 189 Transfer e Decline
Jul-17 190 Transfer e Decline
Jul-17 191 Transfer e Withdraw
Jul-17 192 Transfer e Withdraw
Jul-17 193 Transfer e Decline
Jul-17 194 Transfer e Withdraw
Jul-17 195 Transfer f Decline
Jul-17 196 Transfer f Withdraw
Jul-17 197 Transfer f Decline
Jul-17 198 Transfer f Decline
Jul-17 199 Transfer h Approved
Jul-17 200 Transfer h Withdraw
Jul-17 201 Transfer h Approved
Jul-17 202 Transfer i Approved
Jul-17 203 Transfer k Approved
Jul-17 204 Transfer m Decline
Jul-17 205 Transfer n Approved
Jul-17 206 Transfer o Approved
Jul-17 207 Transfer p Decline
Jul-17 208 Transfer p Decline
Jul-17 209 Transfer p Decline
Jul-17 210 Transfer p Decline
Aug-17 211 Transfer c Decline
Aug-17 212 Transfer c Approved
Aug-17 213 Transfer d Withdraw
Aug-17 214 Transfer d Approved
Aug-17 215 Transfer d Approved
Aug-17 216 Transfer d Withdraw
Aug-17 217 Transfer e Decline
Aug-17 218 Transfer e Withdraw
Aug-17 219 Transfer e Decline
Aug-17 220 Transfer e Decline
Aug-17 221 Transfer e Decline
Aug-17 222 Transfer e Decline
Aug-17 223 Transfer e Withdraw
Aug-17 224 Transfer e Decline
Aug-17 225 Transfer g Approved
Aug-17 226 Transfer g Approved
Aug-17 227 Transfer g Approved
Aug-17 228 Transfer h Withdraw
Aug-17 229 Transfer h Approved
Aug-17 230 Transfer h Approved
Aug-17 231 Transfer i Withdraw
Aug-17 232 Transfer i Decline
Aug-17 233 Transfer i Decline
Aug-17 234 Transfer j Approved
Aug-17 235 Transfer k Approved
Aug-17 236 Transfer k Approved
Aug-17 237 Transfer m Decline
Aug-17 238 Transfer o Approved
Aug-17 239 Transfer p Decline
Aug-17 240 Transfer p Approved
Sep-17 241 Transfer a Withdraw
Sep-17 242 Transfer c Withdraw
Sep-17 243 Transfer d Withdraw
Sep-17 244 Transfer d Withdraw
Sep-17 245 Transfer d Decline
Sep-17 246 Transfer e Withdraw
Sep-17 247 Transfer e Approved
Sep-17 248 Transfer e Withdraw
Sep-17 249 Transfer e Withdraw
Sep-17 250 Transfer e Decline
Sep-17 251 Transfer e Withdraw
Sep-17 252 Transfer f Approved
Sep-17 253 Transfer f Approved
Sep-17 254 Transfer g Decline
Sep-17 255 Transfer g Approved
Sep-17 256 Transfer h Approved
Sep-17 257 Transfer i Approved
Sep-17 258 Transfer k Approved
Sep-17 259 Transfer k Decline
Sep-17 260 Transfer m Decline
Sep-17 261 Transfer n Decline
Sep-17 262 Transfer n Decline
Sep-17 263 Transfer n Decline
Sep-17 264 Transfer o Approved
Sep-17 265 Transfer o Approved
Sep-17 266 Transfer p Approved
Sep-17 267 Transfer p Decline
Sep-17 268 Transfer p Approved
Sep-17 269 Transfer p Approved
Sep-17 270 Transfer p Decline
Oct-17 271 Transfer a Withdraw
Oct-17 272 Transfer b Withdraw
Oct-17 273 Transfer d Approved
Oct-17 274 Transfer d Withdraw
Oct-17 275 Transfer e Decline
Oct-17 276 Transfer e Withdraw
Oct-17 277 Transfer e Decline
Oct-17 278 Transfer e Decline
Oct-17 279 Transfer f Approved
Oct-17 280 Transfer h Approved
Oct-17 281 Transfer h Approved
Oct-17 282 Transfer h Withdraw
Oct-17 283 Transfer h Withdraw
Oct-17 284 Transfer h Approved
Oct-17 285 Transfer i Withdraw
Oct-17 286 Transfer i Approved
Oct-17 287 Transfer i Decline
Oct-17 288 Transfer i Withdraw
Oct-17 289 Transfer i Approved
Oct-17 290 Transfer i Approved
Oct-17 291 Transfer i Approved
Oct-17 292 Transfer j Decline
Oct-17 293 Transfer k Approved
Oct-17 294 Transfer k Approved
Oct-17 295 Transfer m Approved
Oct-17 296 Transfer o Withdraw
Oct-17 297 Transfer p Approved
Oct-17 298 Transfer p Decline
Oct-17 299 Transfer p Approved
Oct-17 300 Transfer p Decline
Nov-17 301 Transfer c Decline
Nov-17 302 Transfer c Approved
Nov-17 303 Transfer c Withdraw
Nov-17 304 Transfer c Approved
Nov-17 305 Transfer d Approved
Nov-17 306 Transfer d Decline
Nov-17 307 Transfer e Withdraw
Nov-17 308 Transfer e Decline
Nov-17 309 Transfer e Decline
Nov-17 310 Transfer f Approved
Nov-17 311 Transfer g Approved
Nov-17 312 Transfer g Decline
Nov-17 313 Transfer g Approved
Nov-17 314 Transfer i Approved
Nov-17 315 Transfer i Approved
Nov-17 316 Transfer i Approved
Nov-17 317 Transfer k Approved
Nov-17 318 Transfer k Approved
Nov-17 319 Transfer k Approved
Nov-17 320 Transfer m Decline
Nov-17 321 Transfer n Withdraw
Nov-17 322 Transfer o Approved
Nov-17 323 Transfer o Approved
Nov-17 324 Transfer o Withdraw
Nov-17 325 Transfer o Approved
Nov-17 326 Transfer p Withdraw
Nov-17 327 Transfer p Approved
Nov-17 328 Transfer p Decline
Nov-17 329 Transfer p Decline
Nov-17 330 Transfer p Decline
Dec-17 331 Transfer d Approved
Dec-17 332 Transfer e Decline
Dec-17 333 Transfer f Decline
Dec-17 334 Transfer p Withdraw
Dec-17 335 Transfer a Approved
Dec-17 336 Transfer a Withdraw
Dec-17 337 Transfer a Decline
Dec-17 338 Transfer b Approved
Dec-17 339 Transfer d Approved
Dec-17 340 Transfer d Decline
Dec-17 341 Transfer d Approved
Dec-17 342 Transfer e Decline
Dec-17 343 Transfer e Withdraw
Dec-17 344 Transfer f Withdraw
Dec-17 345 Transfer f Decline
Dec-17 346 Transfer h Decline
Dec-17 347 Transfer i Approved
Dec-17 348 Transfer i Withdraw
Dec-17 349 Transfer i Approved
Dec-17 350 Transfer i Approved
Dec-17 351 Transfer k Withdraw
Dec-17 352 Transfer k Approved
Dec-17 353 Transfer k Approved
Dec-17 354 Transfer k Approved
Dec-17 355 Transfer l Withdraw
Dec-17 356 Transfer l Decline
Dec-17 357 Transfer n Decline
Dec-17 358 Transfer p Decline
Dec-17 359 Transfer p Approved
Dec-17 360 Transfer p Decline

Solution
‎04-26-2018 05:23 PM
Super User
Posts: 24,028

Re: create a new variable by taking the sum of another variable by group

Here's two ways you can do it. The PROC FREQ will generate results in the table and then the data is actually in the WANT data set, but I've filtered it for only approval rates.

``````data have;
infile cards truncover;
informat date anydtdte.;
format date monyy7.;
input
DATE Record REQUEST \$ CRITERIA \$  DECISION \$;
cards;
Jan-17 1 Transfer a Decline
Jan-17 2 Transfer i Decline
Jan-17 3 Transfer o Approved
Jan-17 4 Transfer a Approved
Jan-17 5 Transfer a Approved
Jan-17 6 Transfer a Approved
Jan-17 7 Transfer a Withdraw
Jan-17 8 Transfer a Decline
Jan-17 9 Transfer a Decline
Jan-17 10 Transfer b Approved
Jan-17 11 Transfer d Withdraw
Jan-17 12 Transfer d Approved
Jan-17 13 Transfer d Decline
Jan-17 14 Transfer d Withdraw
Jan-17 15 Transfer f Approved
Jan-17 16 Transfer f Decline
Jan-17 17 Transfer g Approved
Jan-17 18 Transfer g Decline
Jan-17 19 Transfer g Approved
Jan-17 20 Transfer g Decline
Jan-17 21 Transfer h Decline
Jan-17 22 Transfer h Decline
Jan-17 23 Transfer i Decline
Jan-17 24 Transfer i Decline
Jan-17 25 Transfer k Decline
Jan-17 26 Transfer m Decline
Jan-17 27 Transfer m Withdraw
Jan-17 28 Transfer o Decline
Jan-17 29 Transfer p Approved
Jan-17 30 Transfer p Decline
Feb-17 31 Transfer a Withdraw
Feb-17 32 Transfer a Approved
Feb-17 33 Transfer a Decline
Feb-17 34 Transfer b Withdraw
Feb-17 35 Transfer b Approved
Feb-17 36 Transfer c Approved
Feb-17 37 Transfer c Decline
Feb-17 38 Transfer c Approved
Feb-17 39 Transfer c Decline
Feb-17 40 Transfer d Approved
Feb-17 41 Transfer d Approved
Feb-17 42 Transfer d Approved
Feb-17 43 Transfer e Decline
Feb-17 44 Transfer e Decline
Feb-17 45 Transfer e Withdraw
Feb-17 46 Transfer g Approved
Feb-17 47 Transfer g Approved
Feb-17 48 Transfer h Withdraw
Feb-17 49 Transfer h Approved
Feb-17 50 Transfer i Decline
Feb-17 51 Transfer i Approved
Feb-17 52 Transfer i Decline
Feb-17 53 Transfer k Approved
Feb-17 54 Transfer k Approved
Feb-17 55 Transfer m Withdraw
Feb-17 56 Transfer m Decline
Feb-17 57 Transfer n Decline
Feb-17 58 Transfer p Approved
Feb-17 59 Transfer p Approved
Feb-17 60 Transfer p Withdraw
Mar-17 61 Transfer a Withdraw
Mar-17 62 Transfer b Approved
Mar-17 63 Transfer b Withdraw
Mar-17 64 Transfer c Decline
Mar-17 65 Transfer d Approved
Mar-17 66 Transfer d Decline
Mar-17 67 Transfer d Approved
Mar-17 68 Transfer e Approved
Mar-17 69 Transfer e Decline
Mar-17 70 Transfer e Withdraw
Mar-17 71 Transfer e Decline
Mar-17 72 Transfer e Decline
Mar-17 73 Transfer f Approved
Mar-17 74 Transfer f Decline
Mar-17 75 Transfer f Approved
Mar-17 76 Transfer f Approved
Mar-17 77 Transfer g Approved
Mar-17 78 Transfer g Approved
Mar-17 79 Transfer h Approved
Mar-17 80 Transfer h Approved
Mar-17 81 Transfer i Decline
Mar-17 82 Transfer i Decline
Mar-17 83 Transfer i Approved
Mar-17 84 Transfer k Approved
Mar-17 85 Transfer m Decline
Mar-17 86 Transfer o Decline
Mar-17 87 Transfer p Approved
Mar-17 88 Transfer p Approved
Mar-17 89 Transfer p Decline
Mar-17 90 Transfer p Decline
Apr-17 91 Transfer a Decline
Apr-17 92 Transfer d Withdraw
Apr-17 93 Transfer d Approved
Apr-17 94 Transfer d Approved
Apr-17 95 Transfer d Approved
Apr-17 96 Transfer d Approved
Apr-17 97 Transfer e Decline
Apr-17 98 Transfer e Withdraw
Apr-17 99 Transfer e Withdraw
Apr-17 100 Transfer e Withdraw
Apr-17 101 Transfer f Decline
Apr-17 102 Transfer f Approved
Apr-17 103 Transfer g Decline
Apr-17 104 Transfer g Decline
Apr-17 105 Transfer g Approved
Apr-17 106 Transfer h Withdraw
Apr-17 107 Transfer h Approved
Apr-17 108 Transfer h Decline
Apr-17 109 Transfer i Withdraw
Apr-17 110 Transfer i Approved
Apr-17 111 Transfer i Decline
Apr-17 112 Transfer k Approved
Apr-17 113 Transfer n Withdraw
Apr-17 114 Transfer o Approved
Apr-17 115 Transfer o Approved
Apr-17 116 Transfer p Decline
Apr-17 117 Transfer p Decline
Apr-17 118 Transfer p Decline
Apr-17 119 Transfer p Approved
Apr-17 120 Transfer p Withdraw
May-17 121 Transfer a Withdraw
May-17 122 Transfer a Approved
May-17 123 Transfer a Decline
May-17 124 Transfer a Decline
May-17 125 Transfer b Decline
May-17 126 Transfer c Approved
May-17 127 Transfer c Decline
May-17 128 Transfer d Approved
May-17 129 Transfer d Approved
May-17 130 Transfer e Decline
May-17 131 Transfer e Decline
May-17 132 Transfer h Approved
May-17 133 Transfer h Approved
May-17 134 Transfer h Approved
May-17 135 Transfer i Decline
May-17 136 Transfer i Approved
May-17 137 Transfer k Approved
May-17 138 Transfer k Approved
May-17 139 Transfer k Approved
May-17 140 Transfer k Withdraw
May-17 141 Transfer m Decline
May-17 142 Transfer n Decline
May-17 143 Transfer o Approved
May-17 144 Transfer p Decline
May-17 145 Transfer p Approved
May-17 146 Transfer p Decline
May-17 147 Transfer p Approved
May-17 148 Transfer p Decline
May-17 149 Transfer p Withdraw
May-17 150 Transfer p Decline
Jun-17 151 Transfer a Approved
Jun-17 152 Transfer c Approved
Jun-17 153 Transfer d Decline
Jun-17 154 Transfer d Approved
Jun-17 155 Transfer k Approved
Jun-17 156 Transfer l Withdraw
Jun-17 157 Transfer l Decline
Jun-17 158 Transfer c Decline
Jun-17 159 Transfer e Decline
Jun-17 160 Transfer g Approved
Jun-17 161 Transfer g Decline
Jun-17 162 Transfer h Approved
Jun-17 163 Transfer k Approved
Jun-17 164 Transfer k Approved
Jun-17 165 Transfer m Decline
Jun-17 166 Transfer p Decline
Jun-17 167 Transfer c Withdraw
Jun-17 168 Transfer c Withdraw
Jun-17 169 Transfer k Approved
Jun-17 170 Transfer k Approved
Jun-17 171 Transfer m Decline
Jun-17 172 Transfer o Decline
Jun-17 173 Transfer o Decline
Jun-17 174 Transfer p Decline
Jun-17 175 Transfer p Withdraw
Jun-17 176 Transfer p Approved
Jun-17 177 Transfer p Decline
Jun-17 178 Transfer p Approved
Jun-17 179 Transfer p Approved
Jun-17 180 Transfer p Approved
Jul-17 181 Transfer a Approved
Jul-17 182 Transfer a Decline
Jul-17 183 Transfer a Withdraw
Jul-17 184 Transfer c Withdraw
Jul-17 185 Transfer c Approved
Jul-17 186 Transfer c Withdraw
Jul-17 187 Transfer d Withdraw
Jul-17 188 Transfer d Approved
Jul-17 189 Transfer e Decline
Jul-17 190 Transfer e Decline
Jul-17 191 Transfer e Withdraw
Jul-17 192 Transfer e Withdraw
Jul-17 193 Transfer e Decline
Jul-17 194 Transfer e Withdraw
Jul-17 195 Transfer f Decline
Jul-17 196 Transfer f Withdraw
Jul-17 197 Transfer f Decline
Jul-17 198 Transfer f Decline
Jul-17 199 Transfer h Approved
Jul-17 200 Transfer h Withdraw
Jul-17 201 Transfer h Approved
Jul-17 202 Transfer i Approved
Jul-17 203 Transfer k Approved
Jul-17 204 Transfer m Decline
Jul-17 205 Transfer n Approved
Jul-17 206 Transfer o Approved
Jul-17 207 Transfer p Decline
Jul-17 208 Transfer p Decline
Jul-17 209 Transfer p Decline
Jul-17 210 Transfer p Decline
Aug-17 211 Transfer c Decline
Aug-17 212 Transfer c Approved
Aug-17 213 Transfer d Withdraw
Aug-17 214 Transfer d Approved
Aug-17 215 Transfer d Approved
Aug-17 216 Transfer d Withdraw
Aug-17 217 Transfer e Decline
Aug-17 218 Transfer e Withdraw
Aug-17 219 Transfer e Decline
Aug-17 220 Transfer e Decline
Aug-17 221 Transfer e Decline
Aug-17 222 Transfer e Decline
Aug-17 223 Transfer e Withdraw
Aug-17 224 Transfer e Decline
Aug-17 225 Transfer g Approved
Aug-17 226 Transfer g Approved
Aug-17 227 Transfer g Approved
Aug-17 228 Transfer h Withdraw
Aug-17 229 Transfer h Approved
Aug-17 230 Transfer h Approved
Aug-17 231 Transfer i Withdraw
Aug-17 232 Transfer i Decline
Aug-17 233 Transfer i Decline
Aug-17 234 Transfer j Approved
Aug-17 235 Transfer k Approved
Aug-17 236 Transfer k Approved
Aug-17 237 Transfer m Decline
Aug-17 238 Transfer o Approved
Aug-17 239 Transfer p Decline
Aug-17 240 Transfer p Approved
Sep-17 241 Transfer a Withdraw
Sep-17 242 Transfer c Withdraw
Sep-17 243 Transfer d Withdraw
Sep-17 244 Transfer d Withdraw
Sep-17 245 Transfer d Decline
Sep-17 246 Transfer e Withdraw
Sep-17 247 Transfer e Approved
Sep-17 248 Transfer e Withdraw
Sep-17 249 Transfer e Withdraw
Sep-17 250 Transfer e Decline
Sep-17 251 Transfer e Withdraw
Sep-17 252 Transfer f Approved
Sep-17 253 Transfer f Approved
Sep-17 254 Transfer g Decline
Sep-17 255 Transfer g Approved
Sep-17 256 Transfer h Approved
Sep-17 257 Transfer i Approved
Sep-17 258 Transfer k Approved
Sep-17 259 Transfer k Decline
Sep-17 260 Transfer m Decline
Sep-17 261 Transfer n Decline
Sep-17 262 Transfer n Decline
Sep-17 263 Transfer n Decline
Sep-17 264 Transfer o Approved
Sep-17 265 Transfer o Approved
Sep-17 266 Transfer p Approved
Sep-17 267 Transfer p Decline
Sep-17 268 Transfer p Approved
Sep-17 269 Transfer p Approved
Sep-17 270 Transfer p Decline
Oct-17 271 Transfer a Withdraw
Oct-17 272 Transfer b Withdraw
Oct-17 273 Transfer d Approved
Oct-17 274 Transfer d Withdraw
Oct-17 275 Transfer e Decline
Oct-17 276 Transfer e Withdraw
Oct-17 277 Transfer e Decline
Oct-17 278 Transfer e Decline
Oct-17 279 Transfer f Approved
Oct-17 280 Transfer h Approved
Oct-17 281 Transfer h Approved
Oct-17 282 Transfer h Withdraw
Oct-17 283 Transfer h Withdraw
Oct-17 284 Transfer h Approved
Oct-17 285 Transfer i Withdraw
Oct-17 286 Transfer i Approved
Oct-17 287 Transfer i Decline
Oct-17 288 Transfer i Withdraw
Oct-17 289 Transfer i Approved
Oct-17 290 Transfer i Approved
Oct-17 291 Transfer i Approved
Oct-17 292 Transfer j Decline
Oct-17 293 Transfer k Approved
Oct-17 294 Transfer k Approved
Oct-17 295 Transfer m Approved
Oct-17 296 Transfer o Withdraw
Oct-17 297 Transfer p Approved
Oct-17 298 Transfer p Decline
Oct-17 299 Transfer p Approved
Oct-17 300 Transfer p Decline
Nov-17 301 Transfer c Decline
Nov-17 302 Transfer c Approved
Nov-17 303 Transfer c Withdraw
Nov-17 304 Transfer c Approved
Nov-17 305 Transfer d Approved
Nov-17 306 Transfer d Decline
Nov-17 307 Transfer e Withdraw
Nov-17 308 Transfer e Decline
Nov-17 309 Transfer e Decline
Nov-17 310 Transfer f Approved
Nov-17 311 Transfer g Approved
Nov-17 312 Transfer g Decline
Nov-17 313 Transfer g Approved
Nov-17 314 Transfer i Approved
Nov-17 315 Transfer i Approved
Nov-17 316 Transfer i Approved
Nov-17 317 Transfer k Approved
Nov-17 318 Transfer k Approved
Nov-17 319 Transfer k Approved
Nov-17 320 Transfer m Decline
Nov-17 321 Transfer n Withdraw
Nov-17 322 Transfer o Approved
Nov-17 323 Transfer o Approved
Nov-17 324 Transfer o Withdraw
Nov-17 325 Transfer o Approved
Nov-17 326 Transfer p Withdraw
Nov-17 327 Transfer p Approved
Nov-17 328 Transfer p Decline
Nov-17 329 Transfer p Decline
Nov-17 330 Transfer p Decline
Dec-17 331 Transfer d Approved
Dec-17 332 Transfer e Decline
Dec-17 333 Transfer f Decline
Dec-17 334 Transfer p Withdraw
Dec-17 335 Transfer a Approved
Dec-17 336 Transfer a Withdraw
Dec-17 337 Transfer a Decline
Dec-17 338 Transfer b Approved
Dec-17 339 Transfer d Approved
Dec-17 340 Transfer d Decline
Dec-17 341 Transfer d Approved
Dec-17 342 Transfer e Decline
Dec-17 343 Transfer e Withdraw
Dec-17 344 Transfer f Withdraw
Dec-17 345 Transfer f Decline
Dec-17 346 Transfer h Decline
Dec-17 347 Transfer i Approved
Dec-17 348 Transfer i Withdraw
Dec-17 349 Transfer i Approved
Dec-17 350 Transfer i Approved
Dec-17 351 Transfer k Withdraw
Dec-17 352 Transfer k Approved
Dec-17 353 Transfer k Approved
Dec-17 354 Transfer k Approved
Dec-17 355 Transfer l Withdraw
Dec-17 356 Transfer l Decline
Dec-17 357 Transfer n Decline
Dec-17 358 Transfer p Decline
Dec-17 359 Transfer p Approved
Dec-17 360 Transfer p Decline
;;;;
run;

proc freq data=have ;
by date;
format date monyy7.;
table decision / out=want (where = (decision='Approved')) outpct list;

where decision in ('Approved', 'Decline');
run;

title 'summary of results';
proc print data=want;
run;``````

New Contributor
Posts: 3

Re: create a new variable by taking the sum of another variable by group

thank you very much Reeza this worked perfectly! After 2 days of not knowing where to start you have helped me tremendously

☑ This topic is solved.