how ? you have a syntax example. I admit that I am not an expert in macro   ... View more

yes @ballardw he explained but he didn't say how to get around this problem, and I tell you that I don't know how to do it.. combine the two macros into one? modify the compute..? if yes, how can I meet my needs and not fall into the multiple compute error? ... View more

knew it came from him. but what is the solution to avoid this problem? I don't know what I should fix and how 🤔       ... View more

I have the two logs below. log 1: without macro ===> it works log2: with macro==> in error    LOG:1 10 11 ODS _ALL_ CLOSE; 12 OPTIONS DEV=PNG; 13 GOPTIONS XPIXELS=0 YPIXELS=0; 14 FILENAME EGSR TEMP; 15 ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR 16 STYLE=HTMLBlue 17 STYLESHEET=(URL=file:///C:/Program%20Files%20(x86)/SASHome/x86/SASEnterpriseGuide/7.1/Styles/HTMLBlue.css) 18 NOGTITLE 19 NOGFOOTNOTE 20 GPATH=&sasworklocation 21 ENCODING=UTF8 22 options(rolap="on") 23 ; NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR 24 FILENAME EGXLSSX TEMP; 25 ODS EXCEL(ID=EGXLSSX) FILE=EGXLSSX STYLE=Excel 26 OPTIONS ( 27 EMBEDDED_TITLES="no" EMBEDDED_FOOTNOTES="no" SHEET_INTERVAL="None" 28 ); 29 30 GOPTIONS ACCESSIBLE; 31 32 options mprint; 33 34 PROC FORMAT ; 34 ! PICTURE milliers (ROUND) 35 99999999='-' 36 low - <0 = "000 000 009" (prefix='-') 37 0 - <99999999= "000 000 009" ; NOTE: Format MILLIERS is already on the library WORK.FORMATS. NOTE: Format MILLIERS has been output. 38 RUN ; NOTE: PROCEDURE FORMAT a utilisé (Durée totale du traitement) : real time 0.00 seconds cpu time 0.00 seconds 39 ods listing close ; 40 ods excel file='S:\qs\02. fatyr\kjh\clars.xlsx' style=pearl; 41 42 43 proc report data=TAB split='*' ; 44 column 45 ("~" UNIVERS ) 46 ("_blanc1_" pers_cible) ("_blanc2_" pers_temoin) 47 48 2 Le Système SAS 07:51 Wednesday, December 6, 2023 49 /* CONTACT ENTRANT */ 50 ("ENTRANT" 51 entrant_cible 52 entrant_temoin 53 54 tx_min_cible_entrant 55 tx_max_cible_entrant 56 tx_min_temoin_entrant 57 tx_max_temoin_entrant 58 top_tx_min_cible_entrant 59 top_tx_max_cible_entrant 60 top_tx_min_temoin_entrant 61 top_tx_max_temoin_entrant) 62 63 64 ("NEG" 65 Neg_cible 66 Neg_temoin 67 68 tx_min_cible_Neg 69 tx_max_cible_Neg 70 tx_min_temoin_Neg 71 tx_max_temoin_Neg 72 TOP_tx_max_temoin_Neg 73 TOP_tx_min_temoin_Neg 74 TOP_tx_max_cible_Neg 75 TOP_tx_min_cible_Neg 76 ) 77 78 /* CLIENTS avec HAUSSE de CA */ 79 ("POS" 80 pos_cible 81 pos_temoin 82 83 tx_min_cible_pos 84 tx_max_cible_pos 85 tx_min_temoin_pos 86 tx_max_temoin_pos 87 TOP_tx_max_temoin_pos 88 TOP_tx_min_temoin_pos 89 TOP_tx_max_cible_pos 90 TOP_tx_min_cible_pos 91 ) 92 ; 93 94 define UNIVERS / group order=data style(header)={ width=1IN FOREGROUND=BLACK}; 94 ! define univers /style(column)={width=1IN /*cellwidth=2.5cm*/ fontsize=6pt just=LEFT}; 95 96 define pers_cible / analysis FORMAT=commax12.0 style(header)={ 96 ! cellwidth=1.3cm FOREGROUND=BLACK background=#DCE6F1}; define pers_cible /style(column)={ just=center}; 97 define pers_temoin / analysis FORMAT=commax12.0 style(header)={cellwidth=1.3cm 97 ! FOREGROUND=BLACK background=#DCE6F1}; define pers_temoin /style(column)={ just=center}; 98 99 /* CONTACT ENTRANT */ 100 define entrant_cible / analysis FORMAT=commax12.0 style(header)={ FOREGROUND=BLACK 100 ! background=#DDD9C4}; define entrant_cible /style(column)={just=center}; 101 define entrant_temoin / analysis noprint ; 102 define tx_min_cible_entrant / analysis format=5.4 3 Le Système SAS 07:51 Wednesday, December 6, 2023 102 ! style(header)={cellwidth=2.5cm}; 103 define tx_max_cible_entrant / analysis format=5.4 103 ! style(header)={cellwidth=2.5cm};; 104 define tx_min_temoin_entrant / analysis format=5.4 104 ! style(header)={cellwidth=2.5cm};; 105 define tx_max_temoin_entrant / analysis format=5.4 105 ! style(header)={cellwidth=2.5cm};; 106 107 define top_tx_min_cible_entrant / computed format=5.4 107 ! style(header)={cellwidth=2.5cm};; 108 define top_tx_max_cible_entrant / computed format=5.4 108 ! style(header)={cellwidth=2.5cm};; 109 define top_tx_min_temoin_entrant / computed format=5.4 109 ! style(header)={cellwidth=2.5cm};; 110 define top_tx_max_temoin_entrant / computed format=5.4 110 ! style(header)={cellwidth=2.5cm};; 111 112 113 /* CLIENTS avec PERTE de CA */ 114 define Neg_cible / analysis FORMAT=commax12.0 style(header)={ FOREGROUND=BLACK 114 ! background=#C5D9F1};define Neg_cible / style(column)={just=center}; 115 define Neg_temoin / analysis noprint; 116 define tx_min_cible_Neg / analysis format=5.4 116 ! style(header)={cellwidth=2.5cm};; 117 define tx_max_cible_Neg / analysis format=5.4 117 ! style(header)={cellwidth=2.5cm};; 118 define tx_min_temoin_Neg / analysis format=5.4 118 ! style(header)={cellwidth=2.5cm};; 119 define tx_max_temoin_Neg / analysis format=5.4 119 ! style(header)={cellwidth=2.5cm};; 120 121 define TOP_tx_max_temoin_Neg / computed format=5.4 121 ! style(header)={cellwidth=2.5cm}; 122 define TOP_tx_min_temoin_Neg / computed format=5.4 122 ! style(header)={cellwidth=2.5cm}; 123 define TOP_tx_max_cible_Neg / computed format=5.4 123 ! style(header)={cellwidth=2.5cm}; 124 define TOP_tx_min_cible_Neg / computed format=5.4 124 ! style(header)={cellwidth=2.5cm}; 125 126 /* CLIENTS avec HAUSSE de CA */ 127 define pos_cible / analysis FORMAT=commax12.0 style(header)={ FOREGROUND=BLACK 127 ! background=#8DB4E2};define pos_cible / style(column)={just=center}; 128 define pos_temoin / analysis noprint; 129 define tx_min_cible_pos / analysis format=5.4 style(header)={cellwidth=2.5cm};; 130 define tx_max_cible_pos / analysis format=5.4 style(header)={cellwidth=2.5cm};; 131 define tx_min_temoin_pos / analysis format=5.4 style(header)={cellwidth=2.5cm};; 132 define tx_max_temoin_pos / analysis format=5.4 style(header)={cellwidth=2.5cm};; 133 134 define TOP_tx_max_temoin_pos / computed format=5.4 style(header)={cellwidth=2.5cm}; 135 define TOP_tx_min_temoin_pos / computed format=5.4 style(header)={cellwidth=2.5cm}; 136 define TOP_tx_max_cible_pos / computed format=5.4 style(header)={cellwidth=2.5cm}; 137 define TOP_tx_min_cible_pos / computed format=5.4 style(header)={cellwidth=2.5cm}; 138 139 140 141 compute top_tx_min_cible_entrant; 142 top_tx_min_cible_entrant=tx_min_cible_entrant.SUM; 4 Le Système SAS 07:51 Wednesday, December 6, 2023 143 endcomp; 144 compute top_tx_max_cible_entrant; 145 top_tx_max_cible_entrant =tx_max_cible_entrant.SUM; 146 endcomp; 147 compute top_tx_min_temoin_entrant; 148 top_tx_min_temoin_entrant =tx_min_temoin_entrant.SUM; 149 endcomp; 150 compute top_tx_max_temoin_entrant; 151 top_tx_max_temoin_entrant=tx_max_temoin_entrant.SUM; 152 endcomp; 153 154 155 /*******************/ 156 compute top_tx_min_cible_Neg; 157 top_tx_min_cible_Neg=tx_min_cible_Neg.SUM; 158 endcomp; 159 compute top_tx_max_cible_Neg; 160 top_tx_max_cible_Neg =tx_max_cible_Neg.SUM; 161 endcomp; 162 compute top_tx_min_temoin_Neg; 163 top_tx_min_temoin_Neg =tx_min_temoin_Neg.SUM; 164 endcomp; 165 compute top_tx_max_temoin_Neg; 166 top_tx_max_temoin_Neg=tx_max_temoin_Neg.SUM; 167 endcomp; 168 169 /*******************/ 170 compute top_tx_min_cible_pos; 171 top_tx_min_cible_pos=tx_min_cible_pos.SUM; 172 endcomp; 173 compute top_tx_max_cible_pos; 174 top_tx_max_cible_pos =tx_max_cible_pos.SUM; 175 endcomp; 176 compute top_tx_min_temoin_pos; 177 top_tx_min_temoin_pos =tx_min_temoin_pos.SUM; 178 endcomp; 179 compute top_tx_max_temoin_pos; 180 top_tx_max_temoin_pos=tx_max_temoin_pos.SUM; 181 endcomp; 182 183 break after univers / summarize ; 184 185 compute after univers; 186 length univers $80; 187 univers=catx('','TOTAL',univers); 188 189 190 /* entrant ********************************************************/ 191 /* taux maxi pour temoin */ 192 if pers_temoin.sum > 0 then do; 193 top_tx_max_temoin_entrant = ((entrant_temoin.sum/pers_temoin.sum) + (1.96 * 193 ! sqrt(((entrant_temoin.sum/pers_temoin.sum) * (1 - (entrant_temoin.sum/pers_temoin.sum)) ) / pers_temoin.sum)))*100; 194 end; 195 else top_tx_max_temoin_entrant = .; 196 /*taux mini pour temoin */ 197 if pers_temoin.sum > 0 then do; 198 top_tx_min_temoin_entrant = ((entrant_temoin.sum/pers_temoin.sum) - (1.96 * 198 ! sqrt(((entrant_temoin.sum/pers_temoin.sum) * (1 - (entrant_temoin.sum/pers_temoin.sum))) / pers_temoin.sum)))*100; 5 Le Système SAS 07:51 Wednesday, December 6, 2023 199 end; 200 else top_tx_min_temoin_entrant = .; 201 202 /*taux maxi pour cible */ 203 if pers_cible.sum > 0 then do; 204 top_tx_max_cible_entrant= ((entrant_cible.sum/pers_cible.sum) + (1.96 * sqrt(((entrant_cible.sum/pers_cible.sum) 204 ! * (1 - (entrant_cible.sum/pers_cible.sum))) / pers_cible.sum)))*100; 205 end; 206 else top_tx_max_cible_entrant = .; 207 /*taux mini pour cible */ 208 if pers_cible.sum > 0 then do; 209 top_tx_min_cible_entrant= ((entrant_cible.sum/pers_cible.sum) - (1.96 * sqrt(((entrant_cible.sum/pers_cible.sum) 209 ! * (1 - (entrant_cible.sum/pers_cible.sum))) / pers_cible.sum)))*100; 210 end; 211 else top_tx_min_cible_entrant= .; 212 213 /* Neg ********************************************************/ 214 /* taux maxi pour temoin */ 215 if pers_temoin.sum > 0 then do; 216 top_tx_max_temoin_Neg= ((Neg_temoin.sum/pers_temoin.sum) + (1.96 * sqrt(((Neg_temoin.sum/pers_temoin.sum) * (1 - 216 ! (Neg_temoin.sum/pers_temoin.sum))) / pers_temoin.sum)))*100; 217 end; 218 else top_tx_max_temoin_Neg= .; 219 /*taux mini pour temoin */ 220 if pers_temoin.sum > 0 then do; 221 top_tx_min_temoin_Neg= ((Neg_temoin.sum/pers_temoin.sum) - (1.96 * sqrt(((Neg_temoin.sum/pers_temoin.sum) * (1 - 221 ! (Neg_temoin.sum/pers_temoin.sum))) / pers_temoin.sum)))*100; 222 end; 223 else top_tx_min_temoin_Neg= .; 224 225 /*taux maxi pour cible */ 226 if pers_cible.sum > 0 then do; 227 top_tx_max_cible_Neg= ((Neg_cible.sum/pers_cible.sum) + (1.96 * sqrt(((Neg_cible.sum/pers_cible.sum) * (1 - 227 ! (Neg_cible.sum/pers_cible.sum))) / pers_cible.sum)))*100; 228 end; 229 else top_tx_max_cible_Neg= .; 230 /*taux mini pour cible */ 231 if pers_cible.sum > 0 then do; 232 top_tx_min_cible_Neg= ((Neg_cible.sum/pers_cible.sum) - (1.96 * sqrt(((Neg_cible.sum/pers_cible.sum) * (1 - 232 ! (Neg_cible.sum/pers_cible.sum))) / pers_cible.sum)))*100; 233 end; 234 else top_tx_min_cible_Neg= .; 235 236 /* pos ********************************************************/ 237 /* taux maxi pour temoin */ 238 if pers_temoin.sum > 0 then do; 239 top_tx_max_temoin_pos= ((pos_temoin.sum/pers_temoin.sum) + (1.96 * sqrt(((pos_temoin.sum/pers_temoin.sum) * (1 - 239 ! (pos_temoin.sum/pers_temoin.sum))) / pers_temoin.sum)))*100; 240 end; 241 else top_tx_max_temoin_pos= .; 242 /*taux mini pour temoin */ 243 if pers_temoin.sum > 0 then do; 244 top_tx_min_temoin_pos= ((pos_temoin.sum/pers_temoin.sum) - (1.96 * sqrt(((pos_temoin.sum/pers_temoin.sum) * (1 - 244 ! (pos_temoin.sum/pers_temoin.sum))) / pers_temoin.sum)))*100; 245 end; 246 else top_tx_min_temoin_pos= .; 247 248 /*taux maxi pour cible */ 6 Le Système SAS 07:51 Wednesday, December 6, 2023 249 if pers_cible.sum > 0 then do; 250 top_tx_max_cible_pos= ((pos_cible.sum/pers_cible.sum) + (1.96 * sqrt(((pos_cible.sum/pers_cible.sum) * (1 - 250 ! (pos_cible.sum/pers_cible.sum))) / pers_cible.sum)))*100; 251 end; 252 else top_tx_max_cible_pos= .; 253 /*taux mini pour cible */ 254 if pers_cible.sum > 0 then do; 255 top_tx_min_cible_pos= ((pos_cible.sum/pers_cible.sum) - (1.96 * sqrt(((pos_cible.sum/pers_cible.sum) * (1 - 255 ! (pos_cible.sum/pers_cible.sum))) / pers_cible.sum)))*100; 256 end; 257 else top_tx_min_cible_pos= .; 258 259 260 261 endcomp; 262 run; WARNING: Length of character variable UNIVERS has already been set. Use the LENGTH statement as the very first statement in the DATA STEP to declare the length of a character variable. NOTE: There were 249 observations read from the data set WORK.TAB. NOTE: At least one W.D format was too small for the number to be printed. The decimal may be shifted by the "BEST" format. NOTE: PROCEDURE REPORT a utilisé (Durée totale du traitement) : real time 0.29 seconds cpu time 0.29 seconds 263 ods excel close;   LOG : 2 1 Le Système SAS 07:51 Wednesday, December 6, 2023 10 11 ODS _ALL_ CLOSE; 12 OPTIONS DEV=PNG; 13 GOPTIONS XPIXELS=0 YPIXELS=0; 14 FILENAME EGSR TEMP; 15 ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR 16 STYLE=HTMLBlue 17 STYLESHEET=(URL=file:///C:/Program%20Files%20(x86)/SASHome/x86/SASEnterpriseGuide/7.1/Styles/HTMLBlue.css) 18 NOGTITLE 19 NOGFOOTNOTE 20 GPATH=&sasworklocation 21 ENCODING=UTF8 22 options(rolap="on") 23 ; NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR 24 FILENAME EGXLSSX TEMP; 25 ODS EXCEL(ID=EGXLSSX) FILE=EGXLSSX STYLE=Excel 26 OPTIONS ( 27 EMBEDDED_TITLES="no" EMBEDDED_FOOTNOTES="no" SHEET_INTERVAL="None" 28 ); 29 30 GOPTIONS ACCESSIBLE; 31 32 options mprint; 33 34 PROC FORMAT ; 34 ! PICTURE milliers (ROUND) 35 99999999='-' 36 low - <0 = "000 000 009" (prefix='-') 37 0 - <99999999= "000 000 009" ; NOTE: Format MILLIERS is already on the library WORK.FORMATS. NOTE: Format MILLIERS has been output. 38 RUN ; NOTE: PROCEDURE FORMAT a utilisé (Durée totale du traitement) : real time 0.00 seconds cpu time 0.00 seconds 39 ods listing close ; 40 ods excel file='S:\qs\02. fatyr\kjh\clars.xlsx' style=pearl; 41 42 %macro calc_top_sum(var); 43 /* Calcul de la somme du taux minimum pour le groupe cible */ 44 compute top_tx_min_cible_&var; 45 top_tx_min_cible_&var = tx_min_cible_&var..sum; 46 endcomp; 47 48 /* Calcul de la somme du taux maximum pour le groupe cible */ 2 Le Système SAS 07:51 Wednesday, December 6, 2023 49 compute top_tx_max_cible_&var; 50 top_tx_max_cible_&var = tx_max_cible_&var..sum; 51 endcomp; 52 53 /* Calcul de la somme du taux minimum pour le groupe témoin */ 54 compute top_tx_min_temoin_&var; 55 top_tx_min_temoin_&var = tx_min_temoin_&var..sum; 56 endcomp; 57 58 /* Calcul de la somme du taux maximum pour le groupe témoin */ 59 compute top_tx_max_temoin_&var; 60 top_tx_max_temoin_&var = tx_max_temoin_&var..sum; 61 endcomp; 62 %mend calc_top_sum; 63 64 /********************************************/ 65 %macro calc_top_taux_tot(var); 66 /* Calcul du taux maximum et minimum pour temoin et cible pour &var */ 67 compute top_tx_max_temoin_&var; 68 /* Calculer le taux maximum pour le groupe témoin si le nombre de personnes est supérieur à 0 */ 69 if pers_temoin.sum > 0 then do; 70 top_tx_max_temoin_&var = ((&var._temoin.sum / pers_temoin.sum) + 71 (1.96 * sqrt((&var._temoin.sum / pers_temoin.sum) * 72 (1 - (&var._temoin.sum / pers_temoin.sum))) / pers_temoin.sum)) * 100; 73 end; 74 else top_tx_max_temoin_&var = .; /* Valeur manquante si pas assez de personnes */ 75 endcomp; 76 /* Calculer le taux minimum pour le groupe témoin si le nombre de personnes est supérieur à 0 */ 77 compute top_tx_min_temoin_&var; 78 if pers_temoin.sum > 0 then do; 79 top_tx_min_temoin_&var = ((&var._temoin.sum / pers_temoin.sum) + 80 (1.96 * sqrt((&var._temoin.sum / pers_temoin.sum) * 81 (1 - (&var._temoin.sum / pers_temoin.sum))) / pers_temoin.sum)) * 100; 82 end; 83 else top_tx_min_temoin_&var = .; /* Valeur manquante si pas assez de personnes */ 84 endcomp; 85 /* Calculer le taux maximum pour le groupe cible si le nombre de personnes est supérieur à 0 */ 86 compute top_tx_max_cible_&var; 87 if pers_cible.sum > 0 then do; 88 top_tx_max_cible_&var = ((&var._cible.sum / pers_cible.sum) + 89 (1.96 * sqrt((&var._cible.sum / pers_cible.sum) * 90 (1 - (&var._cible.sum / pers_cible.sum))) / pers_cible.sum)) * 100; 91 end; 92 else top_tx_max_cible_&var = .; /* Valeur manquante si pas assez de personnes */ 93 endcomp; 94 95 /* Calculer le taux minimum pour le groupe cible si le nombre de personnes est supérieur à 0 */ 96 compute top_tx_min_cible_&var; 97 if pers_cible.sum > 0 then do; 98 top_tx_min_cible_&var = ((&var._cible.sum / pers_cible.sum) + 99 (1.96 * sqrt((&var._cible.sum / pers_cible.sum) * 100 (1 - (&var._cible.sum / pers_cible.sum))) / pers_cible.sum)) * 100; 101 end; 102 else top_tx_min_cible_&var = .; /* Valeur manquante si pas assez de personnes */ 103 endcomp; 104 %mend calc_top_taux_tot; 105 106 3 Le Système SAS 07:51 Wednesday, December 6, 2023 107 108 proc report data=TAB split='*' ; 109 column 110 ("~" UNIVERS ) 111 ("_blanc1_" pers_cible) ("_blanc2_" pers_temoin) 112 113 114 /* CONTACT ENTRANT */ 115 ("ENTRANT" 116 entrant_cible 117 entrant_temoin 118 119 tx_min_cible_entrant 120 tx_max_cible_entrant 121 tx_min_temoin_entrant 122 tx_max_temoin_entrant 123 top_tx_min_cible_entrant 124 top_tx_max_cible_entrant 125 top_tx_min_temoin_entrant 126 top_tx_max_temoin_entrant) 127 128 129 ("NEG" 130 Neg_cible 131 Neg_temoin 132 133 tx_min_cible_Neg 134 tx_max_cible_Neg 135 tx_min_temoin_Neg 136 tx_max_temoin_Neg 137 TOP_tx_max_temoin_Neg 138 TOP_tx_min_temoin_Neg 139 TOP_tx_max_cible_Neg 140 TOP_tx_min_cible_Neg 141 ) 142 143 /* CLIENTS avec HAUSSE de CA */ 144 ("POS" 145 pos_cible 146 pos_temoin 147 148 tx_min_cible_pos 149 tx_max_cible_pos 150 tx_min_temoin_pos 151 tx_max_temoin_pos 152 TOP_tx_max_temoin_pos 153 TOP_tx_min_temoin_pos 154 TOP_tx_max_cible_pos 155 TOP_tx_min_cible_pos 156 ) 157 ; 158 159 define UNIVERS / group order=data style(header)={ width=1IN FOREGROUND=BLACK}; 159 ! define univers /style(column)={width=1IN /*cellwidth=2.5cm*/ fontsize=6pt just=LEFT}; 160 161 define pers_cible / analysis FORMAT=commax12.0 style(header)={ 161 ! cellwidth=1.3cm FOREGROUND=BLACK background=#DCE6F1}; define pers_cible /style(column)={ just=center}; 162 define pers_temoin / analysis FORMAT=commax12.0 style(header)={cellwidth=1.3cm 4 Le Système SAS 07:51 Wednesday, December 6, 2023 162 ! FOREGROUND=BLACK background=#DCE6F1}; define pers_temoin /style(column)={ just=center}; 163 164 /* CONTACT ENTRANT */ 165 define entrant_cible / analysis FORMAT=commax12.0 style(header)={ FOREGROUND=BLACK 165 ! background=#DDD9C4}; define entrant_cible /style(column)={just=center}; 166 define entrant_temoin / analysis noprint ; 167 define tx_min_cible_entrant / analysis format=5.4 167 ! style(header)={cellwidth=2.5cm}; 168 define tx_max_cible_entrant / analysis format=5.4 168 ! style(header)={cellwidth=2.5cm};; 169 define tx_min_temoin_entrant / analysis format=5.4 169 ! style(header)={cellwidth=2.5cm};; 170 define tx_max_temoin_entrant / analysis format=5.4 170 ! style(header)={cellwidth=2.5cm};; 171 172 define top_tx_min_cible_entrant / computed format=5.4 172 ! style(header)={cellwidth=2.5cm};; 173 define top_tx_max_cible_entrant / computed format=5.4 173 ! style(header)={cellwidth=2.5cm};; 174 define top_tx_min_temoin_entrant / computed format=5.4 174 ! style(header)={cellwidth=2.5cm};; 175 define top_tx_max_temoin_entrant / computed format=5.4 175 ! style(header)={cellwidth=2.5cm};; 176 177 178 /* CLIENTS avec PERTE de CA */ 179 define Neg_cible / analysis FORMAT=commax12.0 style(header)={ FOREGROUND=BLACK 179 ! background=#C5D9F1};define Neg_cible / style(column)={just=center}; 180 define Neg_temoin / analysis noprint; 181 define tx_min_cible_Neg / analysis format=5.4 181 ! style(header)={cellwidth=2.5cm};; 182 define tx_max_cible_Neg / analysis format=5.4 182 ! style(header)={cellwidth=2.5cm};; 183 define tx_min_temoin_Neg / analysis format=5.4 183 ! style(header)={cellwidth=2.5cm};; 184 define tx_max_temoin_Neg / analysis format=5.4 184 ! style(header)={cellwidth=2.5cm};; 185 186 define TOP_tx_max_temoin_Neg / computed format=5.4 186 ! style(header)={cellwidth=2.5cm}; 187 define TOP_tx_min_temoin_Neg / computed format=5.4 187 ! style(header)={cellwidth=2.5cm}; 188 define TOP_tx_max_cible_Neg / computed format=5.4 188 ! style(header)={cellwidth=2.5cm}; 189 define TOP_tx_min_cible_Neg / computed format=5.4 189 ! style(header)={cellwidth=2.5cm}; 190 191 /* CLIENTS avec HAUSSE de CA */ 192 define pos_cible / analysis FORMAT=commax12.0 style(header)={ FOREGROUND=BLACK 192 ! background=#8DB4E2};define pos_cible / style(column)={just=center}; 193 define pos_temoin / analysis noprint; 194 define tx_min_cible_pos / analysis format=5.4 style(header)={cellwidth=2.5cm};; 195 define tx_max_cible_pos / analysis format=5.4 style(header)={cellwidth=2.5cm};; 196 define tx_min_temoin_pos / analysis format=5.4 style(header)={cellwidth=2.5cm};; 197 define tx_max_temoin_pos / analysis format=5.4 style(header)={cellwidth=2.5cm};; 198 199 define TOP_tx_max_temoin_pos / computed format=5.4 style(header)={cellwidth=2.5cm}; 200 define TOP_tx_min_temoin_pos / computed format=5.4 style(header)={cellwidth=2.5cm}; 5 Le Système SAS 07:51 Wednesday, December 6, 2023 201 define TOP_tx_max_cible_pos / computed format=5.4 style(header)={cellwidth=2.5cm}; 202 define TOP_tx_min_cible_pos / computed format=5.4 style(header)={cellwidth=2.5cm}; 203 204 205 %calc_top_sum(entrant); MPRINT(CALC_TOP_SUM): compute top_tx_min_cible_entrant; MPRINT(CALC_TOP_SUM): top_tx_min_cible_entrant = tx_min_cible_entrant.sum; MPRINT(CALC_TOP_SUM): endcomp; MPRINT(CALC_TOP_SUM): compute top_tx_max_cible_entrant; MPRINT(CALC_TOP_SUM): top_tx_max_cible_entrant = tx_max_cible_entrant.sum; MPRINT(CALC_TOP_SUM): endcomp; MPRINT(CALC_TOP_SUM): compute top_tx_min_temoin_entrant; MPRINT(CALC_TOP_SUM): top_tx_min_temoin_entrant = tx_min_temoin_entrant.sum; MPRINT(CALC_TOP_SUM): endcomp; MPRINT(CALC_TOP_SUM): compute top_tx_max_temoin_entrant; MPRINT(CALC_TOP_SUM): top_tx_max_temoin_entrant = tx_max_temoin_entrant.sum; MPRINT(CALC_TOP_SUM): endcomp; 206 %calc_top_sum(Neg); MPRINT(CALC_TOP_SUM): compute top_tx_min_cible_Neg; MPRINT(CALC_TOP_SUM): top_tx_min_cible_Neg = tx_min_cible_Neg.sum; MPRINT(CALC_TOP_SUM): endcomp; MPRINT(CALC_TOP_SUM): compute top_tx_max_cible_Neg; MPRINT(CALC_TOP_SUM): top_tx_max_cible_Neg = tx_max_cible_Neg.sum; MPRINT(CALC_TOP_SUM): endcomp; MPRINT(CALC_TOP_SUM): compute top_tx_min_temoin_Neg; MPRINT(CALC_TOP_SUM): top_tx_min_temoin_Neg = tx_min_temoin_Neg.sum; MPRINT(CALC_TOP_SUM): endcomp; MPRINT(CALC_TOP_SUM): compute top_tx_max_temoin_Neg; MPRINT(CALC_TOP_SUM): top_tx_max_temoin_Neg = tx_max_temoin_Neg.sum; MPRINT(CALC_TOP_SUM): endcomp; 207 %calc_top_sum(pos); MPRINT(CALC_TOP_SUM): compute top_tx_min_cible_pos; MPRINT(CALC_TOP_SUM): top_tx_min_cible_pos = tx_min_cible_pos.sum; MPRINT(CALC_TOP_SUM): endcomp; MPRINT(CALC_TOP_SUM): compute top_tx_max_cible_pos; MPRINT(CALC_TOP_SUM): top_tx_max_cible_pos = tx_max_cible_pos.sum; MPRINT(CALC_TOP_SUM): endcomp; MPRINT(CALC_TOP_SUM): compute top_tx_min_temoin_pos; MPRINT(CALC_TOP_SUM): top_tx_min_temoin_pos = tx_min_temoin_pos.sum; MPRINT(CALC_TOP_SUM): endcomp; MPRINT(CALC_TOP_SUM): compute top_tx_max_temoin_pos; MPRINT(CALC_TOP_SUM): top_tx_max_temoin_pos = tx_max_temoin_pos.sum; MPRINT(CALC_TOP_SUM): endcomp; 208 209 210 211 break after univers / summarize ; 212 213 compute after univers; 214 length univers $80; 215 univers=catx('','TOTAL',univers); 216 217 %calc_top_taux_tot(entrant); MPRINT(CALC_TOP_TAUX_TOT): compute top_tx_max_temoin_entrant; MPRINT(CALC_TOP_TAUX_TOT): if pers_temoin.sum > 0 then do; MPRINT(CALC_TOP_TAUX_TOT): top_tx_max_temoin_entrant = ((entrant_temoin.sum / pers_temoin.sum) + (1.96 * sqrt((entrant_temoin.sum / pers_temoin.sum) * (1 - (entrant_temoin.sum / pers_temoin.sum))) / pers_temoin.sum)) * 100; MPRINT(CALC_TOP_TAUX_TOT): end; 6 Le Système SAS 07:51 Wednesday, December 6, 2023 MPRINT(CALC_TOP_TAUX_TOT): else top_tx_max_temoin_entrant = .; MPRINT(CALC_TOP_TAUX_TOT): endcomp; MPRINT(CALC_TOP_TAUX_TOT): compute top_tx_min_temoin_entrant; ERROR: There are multiple COMPUTE statements for top_tx_min_temoin_entrant. MPRINT(CALC_TOP_TAUX_TOT): if pers_temoin.sum > 0 then do; MPRINT(CALC_TOP_TAUX_TOT): top_tx_min_temoin_entrant = ((entrant_temoin.sum / pers_temoin.sum) + (1.96 * sqrt((entrant_temoin.sum / pers_temoin.sum) * (1 - (entrant_temoin.sum / pers_temoin.sum))) / pers_temoin.sum)) * 100; MPRINT(CALC_TOP_TAUX_TOT): end; MPRINT(CALC_TOP_TAUX_TOT): else top_tx_min_temoin_entrant = .; MPRINT(CALC_TOP_TAUX_TOT): endcomp; MPRINT(CALC_TOP_TAUX_TOT): compute top_tx_max_cible_entrant; MPRINT(CALC_TOP_TAUX_TOT): if pers_cible.sum > 0 then do; MPRINT(CALC_TOP_TAUX_TOT): top_tx_max_cible_entrant = ((entrant_cible.sum / pers_cible.sum) + (1.96 * sqrt((entrant_cible.sum / pers_cible.sum) * (1 - (entrant_cible.sum / pers_cible.sum))) / pers_cible.sum)) * 100; MPRINT(CALC_TOP_TAUX_TOT): end; MPRINT(CALC_TOP_TAUX_TOT): else top_tx_max_cible_entrant = .; MPRINT(CALC_TOP_TAUX_TOT): endcomp; MPRINT(CALC_TOP_TAUX_TOT): compute top_tx_min_cible_entrant; MPRINT(CALC_TOP_TAUX_TOT): if pers_cible.sum > 0 then do; MPRINT(CALC_TOP_TAUX_TOT): top_tx_min_cible_entrant = ((entrant_cible.sum / pers_cible.sum) + (1.96 * sqrt((entrant_cible.sum / pers_cible.sum) * (1 - (entrant_cible.sum / pers_cible.sum))) / pers_cible.sum)) * 100; MPRINT(CALC_TOP_TAUX_TOT): end; MPRINT(CALC_TOP_TAUX_TOT): else top_tx_min_cible_entrant = .; MPRINT(CALC_TOP_TAUX_TOT): endcomp; 218 %calc_top_taux_tot(Neg); MPRINT(CALC_TOP_TAUX_TOT): compute top_tx_max_temoin_Neg; MPRINT(CALC_TOP_TAUX_TOT): if pers_temoin.sum > 0 then do; MPRINT(CALC_TOP_TAUX_TOT): top_tx_max_temoin_Neg = ((Neg_temoin.sum / pers_temoin.sum) + (1.96 * sqrt((Neg_temoin.sum / pers_temoin.sum) * (1 - (Neg_temoin.sum / pers_temoin.sum))) / pers_temoin.sum)) * 100; MPRINT(CALC_TOP_TAUX_TOT): end; MPRINT(CALC_TOP_TAUX_TOT): else top_tx_max_temoin_Neg = .; MPRINT(CALC_TOP_TAUX_TOT): endcomp; MPRINT(CALC_TOP_TAUX_TOT): compute top_tx_min_temoin_Neg; MPRINT(CALC_TOP_TAUX_TOT): if pers_temoin.sum > 0 then do; MPRINT(CALC_TOP_TAUX_TOT): top_tx_min_temoin_Neg = ((Neg_temoin.sum / pers_temoin.sum) + (1.96 * sqrt((Neg_temoin.sum / pers_temoin.sum) * (1 - (Neg_temoin.sum / pers_temoin.sum))) / pers_temoin.sum)) * 100; MPRINT(CALC_TOP_TAUX_TOT): end; MPRINT(CALC_TOP_TAUX_TOT): else top_tx_min_temoin_Neg = .; MPRINT(CALC_TOP_TAUX_TOT): endcomp; MPRINT(CALC_TOP_TAUX_TOT): compute top_tx_max_cible_Neg; MPRINT(CALC_TOP_TAUX_TOT): if pers_cible.sum > 0 then do; MPRINT(CALC_TOP_TAUX_TOT): top_tx_max_cible_Neg = ((Neg_cible.sum / pers_cible.sum) + (1.96 * sqrt((Neg_cible.sum / pers_cible.sum) * (1 - (Neg_cible.sum / pers_cible.sum))) / pers_cible.sum)) * 100; MPRINT(CALC_TOP_TAUX_TOT): end; MPRINT(CALC_TOP_TAUX_TOT): else top_tx_max_cible_Neg = .; MPRINT(CALC_TOP_TAUX_TOT): endcomp; MPRINT(CALC_TOP_TAUX_TOT): compute top_tx_min_cible_Neg; MPRINT(CALC_TOP_TAUX_TOT): if pers_cible.sum > 0 then do; MPRINT(CALC_TOP_TAUX_TOT): top_tx_min_cible_Neg = ((Neg_cible.sum / pers_cible.sum) + (1.96 * sqrt((Neg_cible.sum / pers_cible.sum) * (1 - (Neg_cible.sum / pers_cible.sum))) / pers_cible.sum)) * 100; MPRINT(CALC_TOP_TAUX_TOT): end; MPRINT(CALC_TOP_TAUX_TOT): else top_tx_min_cible_Neg = .; MPRINT(CALC_TOP_TAUX_TOT): endcomp; 219 %calc_top_taux_tot(pos); MPRINT(CALC_TOP_TAUX_TOT): compute top_tx_max_temoin_pos; MPRINT(CALC_TOP_TAUX_TOT): if pers_temoin.sum > 0 then do; MPRINT(CALC_TOP_TAUX_TOT): top_tx_max_temoin_pos = ((pos_temoin.sum / pers_temoin.sum) + (1.96 * sqrt((pos_temoin.sum / pers_temoin.sum) * (1 - (pos_temoin.sum / pers_temoin.sum))) / pers_temoin.sum)) * 100; 7 Le Système SAS 07:51 Wednesday, December 6, 2023 MPRINT(CALC_TOP_TAUX_TOT): end; MPRINT(CALC_TOP_TAUX_TOT): else top_tx_max_temoin_pos = .; MPRINT(CALC_TOP_TAUX_TOT): endcomp; MPRINT(CALC_TOP_TAUX_TOT): compute top_tx_min_temoin_pos; MPRINT(CALC_TOP_TAUX_TOT): if pers_temoin.sum > 0 then do; MPRINT(CALC_TOP_TAUX_TOT): top_tx_min_temoin_pos = ((pos_temoin.sum / pers_temoin.sum) + (1.96 * sqrt((pos_temoin.sum / pers_temoin.sum) * (1 - (pos_temoin.sum / pers_temoin.sum))) / pers_temoin.sum)) * 100; MPRINT(CALC_TOP_TAUX_TOT): end; MPRINT(CALC_TOP_TAUX_TOT): else top_tx_min_temoin_pos = .; MPRINT(CALC_TOP_TAUX_TOT): endcomp; MPRINT(CALC_TOP_TAUX_TOT): compute top_tx_max_cible_pos; MPRINT(CALC_TOP_TAUX_TOT): if pers_cible.sum > 0 then do; MPRINT(CALC_TOP_TAUX_TOT): top_tx_max_cible_pos = ((pos_cible.sum / pers_cible.sum) + (1.96 * sqrt((pos_cible.sum / pers_cible.sum) * (1 - (pos_cible.sum / pers_cible.sum))) / pers_cible.sum)) * 100; MPRINT(CALC_TOP_TAUX_TOT): end; MPRINT(CALC_TOP_TAUX_TOT): else top_tx_max_cible_pos = .; MPRINT(CALC_TOP_TAUX_TOT): endcomp; MPRINT(CALC_TOP_TAUX_TOT): compute top_tx_min_cible_pos; MPRINT(CALC_TOP_TAUX_TOT): if pers_cible.sum > 0 then do; MPRINT(CALC_TOP_TAUX_TOT): top_tx_min_cible_pos = ((pos_cible.sum / pers_cible.sum) + (1.96 * sqrt((pos_cible.sum / pers_cible.sum) * (1 - (pos_cible.sum / pers_cible.sum))) / pers_cible.sum)) * 100; MPRINT(CALC_TOP_TAUX_TOT): end; MPRINT(CALC_TOP_TAUX_TOT): else top_tx_min_cible_pos = .; MPRINT(CALC_TOP_TAUX_TOT): endcomp; 220 221 endcomp; 222 run; NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE REPORT a utilisé (Durée totale du traitement) : real time 0.00 seconds cpu time 0.00 seconds 223 ods excel close; NOTE: Writing EXCEL file: ./S:\qs\02. fatyr\kjh\clars.xlsx' 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 8 Le Système SAS 07:51 Wednesday, December 6, 2023 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 9 Le Système SAS 07:51 Wednesday, December 6, 2023 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 10 Le Système SAS 07:51 Wednesday, December 6, 2023 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 11 Le Système SAS 07:51 Wednesday, December 6, 2023 421 422 423 424 425 426 427 428 GOPTIONS NOACCESSIBLE; 429 %LET _CLIENTTASKLABEL=; 430 %LET _CLIENTPROCESSFLOWNAME=; 431 %LET _CLIENTPROJECTPATH=; 432 %LET _CLIENTPROJECTPATHHOST=; 433 %LET _CLIENTPROJECTNAME=; 434 %LET _SASPROGRAMFILE=; 435 %LET _SASPROGRAMFILEHOST=; 436 437 ;*';*";*/;quit;run; 438 ODS _ALL_ CLOSE; NOTE: Writing EXCEL(EGXLSSX) file: /saswork_lev1/sasworkTP/SAS_workEF97000549CE_lx20015.posix.covea.priv/#LN00515 439 440 441 QUIT; RUN; 442 /********* code without macro : ok *************************/ options mprint; PROC FORMAT ; PICTURE milliers (ROUND) 99999999='-' low - <0 = "000 000 009" (prefix='-') 0 - <99999999= "000 000 009" ; RUN ; ods listing close ; ods excel file='S:\qs\02. fatyr\kjh\clars.xlsx' style=pearl; proc report data=TAB split='*' ; column ("~" UNIVERS ) ("_blanc1_" pers_cible) ("_blanc2_" pers_temoin) /* CONTACT ENTRANT */ ("ENTRANT" entrant_cible entrant_temoin tx_min_cible_entrant tx_max_cible_entrant tx_min_temoin_entrant tx_max_temoin_entrant top_tx_min_cible_entrant top_tx_max_cible_entrant top_tx_min_temoin_entrant top_tx_max_temoin_entrant) ("NEG" Neg_cible Neg_temoin tx_min_cible_Neg tx_max_cible_Neg tx_min_temoin_Neg tx_max_temoin_Neg TOP_tx_max_temoin_Neg TOP_tx_min_temoin_Neg TOP_tx_max_cible_Neg TOP_tx_min_cible_Neg ) /* CLIENTS avec HAUSSE de CA */ ("POS" pos_cible pos_temoin tx_min_cible_pos tx_max_cible_pos tx_min_temoin_pos tx_max_temoin_pos TOP_tx_max_temoin_pos TOP_tx_min_temoin_pos TOP_tx_max_cible_pos TOP_tx_min_cible_pos ) ; define UNIVERS / group order=data style(header)={ width=1IN FOREGROUND=BLACK};define univers /style(column)={width=1IN /*cellwidth=2.5cm*/ fontsize=6pt just=LEFT}; define pers_cible / analysis FORMAT=commax12.0 style(header)={ cellwidth=1.3cm FOREGROUND=BLACK background=#DCE6F1}; define pers_cible /style(column)={ just=center}; define pers_temoin / analysis FORMAT=commax12.0 style(header)={cellwidth=1.3cm FOREGROUND=BLACK background=#DCE6F1}; define pers_temoin /style(column)={ just=center}; /* CONTACT ENTRANT */ define entrant_cible / analysis FORMAT=commax12.0 style(header)={ FOREGROUND=BLACK background=#DDD9C4}; define entrant_cible /style(column)={just=center}; define entrant_temoin / analysis noprint ; define tx_min_cible_entrant / analysis format=5.4 style(header)={cellwidth=2.5cm}; define tx_max_cible_entrant / analysis format=5.4 style(header)={cellwidth=2.5cm};; define tx_min_temoin_entrant / analysis format=5.4 style(header)={cellwidth=2.5cm};; define tx_max_temoin_entrant / analysis format=5.4 style(header)={cellwidth=2.5cm};; define top_tx_min_cible_entrant / computed format=5.4 style(header)={cellwidth=2.5cm};; define top_tx_max_cible_entrant / computed format=5.4 style(header)={cellwidth=2.5cm};; define top_tx_min_temoin_entrant / computed format=5.4 style(header)={cellwidth=2.5cm};; define top_tx_max_temoin_entrant / computed format=5.4 style(header)={cellwidth=2.5cm};; /* CLIENTS avec PERTE de CA */ define Neg_cible / analysis FORMAT=commax12.0 style(header)={ FOREGROUND=BLACK background=#C5D9F1};define Neg_cible / style(column)={just=center}; define Neg_temoin / analysis noprint; define tx_min_cible_Neg / analysis format=5.4 style(header)={cellwidth=2.5cm};; define tx_max_cible_Neg / analysis format=5.4 style(header)={cellwidth=2.5cm};; define tx_min_temoin_Neg / analysis format=5.4 style(header)={cellwidth=2.5cm};; define tx_max_temoin_Neg / analysis format=5.4 style(header)={cellwidth=2.5cm};; define TOP_tx_max_temoin_Neg / computed format=5.4 style(header)={cellwidth=2.5cm}; define TOP_tx_min_temoin_Neg / computed format=5.4 style(header)={cellwidth=2.5cm}; define TOP_tx_max_cible_Neg / computed format=5.4 style(header)={cellwidth=2.5cm}; define TOP_tx_min_cible_Neg / computed format=5.4 style(header)={cellwidth=2.5cm}; /* CLIENTS avec HAUSSE de CA */ define pos_cible / analysis FORMAT=commax12.0 style(header)={ FOREGROUND=BLACK background=#8DB4E2};define pos_cible / style(column)={just=center}; define pos_temoin / analysis noprint; define tx_min_cible_pos / analysis format=5.4 style(header)={cellwidth=2.5cm};; define tx_max_cible_pos / analysis format=5.4 style(header)={cellwidth=2.5cm};; define tx_min_temoin_pos / analysis format=5.4 style(header)={cellwidth=2.5cm};; define tx_max_temoin_pos / analysis format=5.4 style(header)={cellwidth=2.5cm};; define TOP_tx_max_temoin_pos / computed format=5.4 style(header)={cellwidth=2.5cm}; define TOP_tx_min_temoin_pos / computed format=5.4 style(header)={cellwidth=2.5cm}; define TOP_tx_max_cible_pos / computed format=5.4 style(header)={cellwidth=2.5cm}; define TOP_tx_min_cible_pos / computed format=5.4 style(header)={cellwidth=2.5cm}; compute top_tx_min_cible_entrant; top_tx_min_cible_entrant=tx_min_cible_entrant.SUM; endcomp; compute top_tx_max_cible_entrant; top_tx_max_cible_entrant =tx_max_cible_entrant.SUM; endcomp; compute top_tx_min_temoin_entrant; top_tx_min_temoin_entrant =tx_min_temoin_entrant.SUM; endcomp; compute top_tx_max_temoin_entrant; top_tx_max_temoin_entrant=tx_max_temoin_entrant.SUM; endcomp; /*******************/ compute top_tx_min_cible_Neg; top_tx_min_cible_Neg=tx_min_cible_Neg.SUM; endcomp; compute top_tx_max_cible_Neg; top_tx_max_cible_Neg =tx_max_cible_Neg.SUM; endcomp; compute top_tx_min_temoin_Neg; top_tx_min_temoin_Neg =tx_min_temoin_Neg.SUM; endcomp; compute top_tx_max_temoin_Neg; top_tx_max_temoin_Neg=tx_max_temoin_Neg.SUM; endcomp; /*******************/ compute top_tx_min_cible_pos; top_tx_min_cible_pos=tx_min_cible_pos.SUM; endcomp; compute top_tx_max_cible_pos; top_tx_max_cible_pos =tx_max_cible_pos.SUM; endcomp; compute top_tx_min_temoin_pos; top_tx_min_temoin_pos =tx_min_temoin_pos.SUM; endcomp; compute top_tx_max_temoin_pos; top_tx_max_temoin_pos=tx_max_temoin_pos.SUM; endcomp; break after univers / summarize ; compute after univers; length univers $80; univers=catx('','TOTAL',univers); /* entrant ********************************************************/ /* taux maxi pour temoin */ if pers_temoin.sum > 0 then do; top_tx_max_temoin_entrant = ((entrant_temoin.sum/pers_temoin.sum) + (1.96 * sqrt(((entrant_temoin.sum/pers_temoin.sum) * (1 - (entrant_temoin.sum/pers_temoin.sum)) ) / pers_temoin.sum)))*100; end; else top_tx_max_temoin_entrant = .; /*taux mini pour temoin */ if pers_temoin.sum > 0 then do; top_tx_min_temoin_entrant = ((entrant_temoin.sum/pers_temoin.sum) - (1.96 * sqrt(((entrant_temoin.sum/pers_temoin.sum) * (1 - (entrant_temoin.sum/pers_temoin.sum))) / pers_temoin.sum)))*100; end; else top_tx_min_temoin_entrant = .; /*taux maxi pour cible */ if pers_cible.sum > 0 then do; top_tx_max_cible_entrant= ((entrant_cible.sum/pers_cible.sum) + (1.96 * sqrt(((entrant_cible.sum/pers_cible.sum) * (1 - (entrant_cible.sum/pers_cible.sum))) / pers_cible.sum)))*100; end; else top_tx_max_cible_entrant = .; /*taux mini pour cible */ if pers_cible.sum > 0 then do; top_tx_min_cible_entrant= ((entrant_cible.sum/pers_cible.sum) - (1.96 * sqrt(((entrant_cible.sum/pers_cible.sum) * (1 - (entrant_cible.sum/pers_cible.sum))) / pers_cible.sum)))*100; end; else top_tx_min_cible_entrant= .; /* Neg ********************************************************/ /* taux maxi pour temoin */ if pers_temoin.sum > 0 then do; top_tx_max_temoin_Neg= ((Neg_temoin.sum/pers_temoin.sum) + (1.96 * sqrt(((Neg_temoin.sum/pers_temoin.sum) * (1 - (Neg_temoin.sum/pers_temoin.sum))) / pers_temoin.sum)))*100; end; else top_tx_max_temoin_Neg= .; /*taux mini pour temoin */ if pers_temoin.sum > 0 then do; top_tx_min_temoin_Neg= ((Neg_temoin.sum/pers_temoin.sum) - (1.96 * sqrt(((Neg_temoin.sum/pers_temoin.sum) * (1 - (Neg_temoin.sum/pers_temoin.sum))) / pers_temoin.sum)))*100; end; else top_tx_min_temoin_Neg= .; /*taux maxi pour cible */ if pers_cible.sum > 0 then do; top_tx_max_cible_Neg= ((Neg_cible.sum/pers_cible.sum) + (1.96 * sqrt(((Neg_cible.sum/pers_cible.sum) * (1 - (Neg_cible.sum/pers_cible.sum))) / pers_cible.sum)))*100; end; else top_tx_max_cible_Neg= .; /*taux mini pour cible */ if pers_cible.sum > 0 then do; top_tx_min_cible_Neg= ((Neg_cible.sum/pers_cible.sum) - (1.96 * sqrt(((Neg_cible.sum/pers_cible.sum) * (1 - (Neg_cible.sum/pers_cible.sum))) / pers_cible.sum)))*100; end; else top_tx_min_cible_Neg= .; /* pos ********************************************************/ /* taux maxi pour temoin */ if pers_temoin.sum > 0 then do; top_tx_max_temoin_pos= ((pos_temoin.sum/pers_temoin.sum) + (1.96 * sqrt(((pos_temoin.sum/pers_temoin.sum) * (1 - (pos_temoin.sum/pers_temoin.sum))) / pers_temoin.sum)))*100; end; else top_tx_max_temoin_pos= .; /*taux mini pour temoin */ if pers_temoin.sum > 0 then do; top_tx_min_temoin_pos= ((pos_temoin.sum/pers_temoin.sum) - (1.96 * sqrt(((pos_temoin.sum/pers_temoin.sum) * (1 - (pos_temoin.sum/pers_temoin.sum))) / pers_temoin.sum)))*100; end; else top_tx_min_temoin_pos= .; /*taux maxi pour cible */ if pers_cible.sum > 0 then do; top_tx_max_cible_pos= ((pos_cible.sum/pers_cible.sum) + (1.96 * sqrt(((pos_cible.sum/pers_cible.sum) * (1 - (pos_cible.sum/pers_cible.sum))) / pers_cible.sum)))*100; end; else top_tx_max_cible_pos= .; /*taux mini pour cible */ if pers_cible.sum > 0 then do; top_tx_min_cible_pos= ((pos_cible.sum/pers_cible.sum) - (1.96 * sqrt(((pos_cible.sum/pers_cible.sum) * (1 - (pos_cible.sum/pers_cible.sum))) / pers_cible.sum)))*100; end; else top_tx_min_cible_pos= .; endcomp; run; ods excel close; /********* code with macro : KO *************************/ options mprint; PROC FORMAT ; PICTURE milliers (ROUND) 99999999='-' low - <0 = "000 000 009" (prefix='-') 0 - <99999999= "000 000 009" ; RUN ; ods listing close ; ods excel file='S:\qs\02. fatyr\kjh\clars.xlsx' style=pearl; %macro calc_top_sum(var); /* Calcul de la somme du taux minimum pour le groupe cible */ compute top_tx_min_cible_&var; top_tx_min_cible_&var = tx_min_cible_&var..sum; endcomp; /* Calcul de la somme du taux maximum pour le groupe cible */ compute top_tx_max_cible_&var; top_tx_max_cible_&var = tx_max_cible_&var..sum; endcomp; /* Calcul de la somme du taux minimum pour le groupe témoin */ compute top_tx_min_temoin_&var; top_tx_min_temoin_&var = tx_min_temoin_&var..sum; endcomp; /* Calcul de la somme du taux maximum pour le groupe témoin */ compute top_tx_max_temoin_&var; top_tx_max_temoin_&var = tx_max_temoin_&var..sum; endcomp; %mend calc_top_sum; /********************************************/ %macro calc_top_taux_tot(var); /* Calcul du taux maximum et minimum pour temoin et cible pour &var */ compute top_tx_max_temoin_&var; /* Calculer le taux maximum pour le groupe témoin si le nombre de personnes est supérieur à 0 */ if pers_temoin.sum > 0 then do; top_tx_max_temoin_&var = ((&var._temoin.sum / pers_temoin.sum) + (1.96 * sqrt((&var._temoin.sum / pers_temoin.sum) * (1 - (&var._temoin.sum / pers_temoin.sum))) / pers_temoin.sum)) * 100; end; else top_tx_max_temoin_&var = .; /* Valeur manquante si pas assez de personnes */ endcomp; /* Calculer le taux minimum pour le groupe témoin si le nombre de personnes est supérieur à 0 */ compute top_tx_min_temoin_&var; if pers_temoin.sum > 0 then do; top_tx_min_temoin_&var = ((&var._temoin.sum / pers_temoin.sum) + (1.96 * sqrt((&var._temoin.sum / pers_temoin.sum) * (1 - (&var._temoin.sum / pers_temoin.sum))) / pers_temoin.sum)) * 100; end; else top_tx_min_temoin_&var = .; /* Valeur manquante si pas assez de personnes */ endcomp; /* Calculer le taux maximum pour le groupe cible si le nombre de personnes est supérieur à 0 */ compute top_tx_max_cible_&var; if pers_cible.sum > 0 then do; top_tx_max_cible_&var = ((&var._cible.sum / pers_cible.sum) + (1.96 * sqrt((&var._cible.sum / pers_cible.sum) * (1 - (&var._cible.sum / pers_cible.sum))) / pers_cible.sum)) * 100; end; else top_tx_max_cible_&var = .; /* Valeur manquante si pas assez de personnes */ endcomp; /* Calculer le taux minimum pour le groupe cible si le nombre de personnes est supérieur à 0 */ compute top_tx_min_cible_&var; if pers_cible.sum > 0 then do; top_tx_min_cible_&var = ((&var._cible.sum / pers_cible.sum) + (1.96 * sqrt((&var._cible.sum / pers_cible.sum) * (1 - (&var._cible.sum / pers_cible.sum))) / pers_cible.sum)) * 100; end; else top_tx_min_cible_&var = .; /* Valeur manquante si pas assez de personnes */ endcomp; %mend calc_top_taux_tot; proc report data=TAB split='*' ; column ("~" UNIVERS ) ("_blanc1_" pers_cible) ("_blanc2_" pers_temoin) /* CONTACT ENTRANT */ ("ENTRANT" entrant_cible entrant_temoin tx_min_cible_entrant tx_max_cible_entrant tx_min_temoin_entrant tx_max_temoin_entrant top_tx_min_cible_entrant top_tx_max_cible_entrant top_tx_min_temoin_entrant top_tx_max_temoin_entrant) ("NEG" Neg_cible Neg_temoin tx_min_cible_Neg tx_max_cible_Neg tx_min_temoin_Neg tx_max_temoin_Neg TOP_tx_max_temoin_Neg TOP_tx_min_temoin_Neg TOP_tx_max_cible_Neg TOP_tx_min_cible_Neg ) /* CLIENTS avec HAUSSE de CA */ ("POS" pos_cible pos_temoin tx_min_cible_pos tx_max_cible_pos tx_min_temoin_pos tx_max_temoin_pos TOP_tx_max_temoin_pos TOP_tx_min_temoin_pos TOP_tx_max_cible_pos TOP_tx_min_cible_pos ) ; define UNIVERS / group order=data style(header)={ width=1IN FOREGROUND=BLACK};define univers /style(column)={width=1IN /*cellwidth=2.5cm*/ fontsize=6pt just=LEFT}; define pers_cible / analysis FORMAT=commax12.0 style(header)={ cellwidth=1.3cm FOREGROUND=BLACK background=#DCE6F1}; define pers_cible /style(column)={ just=center}; define pers_temoin / analysis FORMAT=commax12.0 style(header)={cellwidth=1.3cm FOREGROUND=BLACK background=#DCE6F1}; define pers_temoin /style(column)={ just=center}; /* CONTACT ENTRANT */ define entrant_cible / analysis FORMAT=commax12.0 style(header)={ FOREGROUND=BLACK background=#DDD9C4}; define entrant_cible /style(column)={just=center}; define entrant_temoin / analysis noprint ; define tx_min_cible_entrant / analysis format=5.4 style(header)={cellwidth=2.5cm}; define tx_max_cible_entrant / analysis format=5.4 style(header)={cellwidth=2.5cm};; define tx_min_temoin_entrant / analysis format=5.4 style(header)={cellwidth=2.5cm};; define tx_max_temoin_entrant / analysis format=5.4 style(header)={cellwidth=2.5cm};; define top_tx_min_cible_entrant / computed format=5.4 style(header)={cellwidth=2.5cm};; define top_tx_max_cible_entrant / computed format=5.4 style(header)={cellwidth=2.5cm};; define top_tx_min_temoin_entrant / computed format=5.4 style(header)={cellwidth=2.5cm};; define top_tx_max_temoin_entrant / computed format=5.4 style(header)={cellwidth=2.5cm};; /* CLIENTS avec PERTE de CA */ define Neg_cible / analysis FORMAT=commax12.0 style(header)={ FOREGROUND=BLACK background=#C5D9F1};define Neg_cible / style(column)={just=center}; define Neg_temoin / analysis noprint; define tx_min_cible_Neg / analysis format=5.4 style(header)={cellwidth=2.5cm};; define tx_max_cible_Neg / analysis format=5.4 style(header)={cellwidth=2.5cm};; define tx_min_temoin_Neg / analysis format=5.4 style(header)={cellwidth=2.5cm};; define tx_max_temoin_Neg / analysis format=5.4 style(header)={cellwidth=2.5cm};; define TOP_tx_max_temoin_Neg / computed format=5.4 style(header)={cellwidth=2.5cm}; define TOP_tx_min_temoin_Neg / computed format=5.4 style(header)={cellwidth=2.5cm}; define TOP_tx_max_cible_Neg / computed format=5.4 style(header)={cellwidth=2.5cm}; define TOP_tx_min_cible_Neg / computed format=5.4 style(header)={cellwidth=2.5cm}; /* CLIENTS avec HAUSSE de CA */ define pos_cible / analysis FORMAT=commax12.0 style(header)={ FOREGROUND=BLACK background=#8DB4E2};define pos_cible / style(column)={just=center}; define pos_temoin / analysis noprint; define tx_min_cible_pos / analysis format=5.4 style(header)={cellwidth=2.5cm};; define tx_max_cible_pos / analysis format=5.4 style(header)={cellwidth=2.5cm};; define tx_min_temoin_pos / analysis format=5.4 style(header)={cellwidth=2.5cm};; define tx_max_temoin_pos / analysis format=5.4 style(header)={cellwidth=2.5cm};; define TOP_tx_max_temoin_pos / computed format=5.4 style(header)={cellwidth=2.5cm}; define TOP_tx_min_temoin_pos / computed format=5.4 style(header)={cellwidth=2.5cm}; define TOP_tx_max_cible_pos / computed format=5.4 style(header)={cellwidth=2.5cm}; define TOP_tx_min_cible_pos / computed format=5.4 style(header)={cellwidth=2.5cm}; %calc_top_sum(entrant); %calc_top_sum(Neg); %calc_top_sum(pos); break after univers / summarize ; compute after univers; length univers $80; univers=catx('','TOTAL',univers); %calc_top_taux_tot(entrant); %calc_top_taux_tot(Neg); %calc_top_taux_tot(pos); endcomp; run; ods excel close;   ... View more

 if I use my hard report proc, it works!! there is no problem, I tell myself that maybe I am using the two macros wrong?? !! for my hard copy use, I need to calculate my break and then copy my values ​​from my tx... .sum columns into my top columns...   proc report data=TAB_ANALYSE ; column ("~" ZZ ) ("_blanc1_" nb_siz_cible) ("CC" CTT_cible tx_min_cible_CTT tx_max_cible_CTT top_tx_min_cible_CTT top_tx_max_cible_CTT ) ("BB" Evol_neg_cible tx_min_cible_Evol_neg tx_max_cible_Evol_neg TOP_tx_max_cible_Evol_neg TOP_tx_min_cible_Evol_neg) ("DD" Evol_pos_cible tx_min_cible_Evol_pos tx_max_cible_Evol_pos TOP_tx_max_cible_Evol_pos TOP_tx_min_cible_Evol_pos); define ZZ / group order=data ; define nb_siz_cible / analysis ; define CTT_cible / analysis ; define tx_min_cible_CTT / analysis ; define tx_max_cible_CTT / analysis ; define top_tx_min_cible_CTT / computed ; define top_tx_max_cible_CTT / computed ; define Evol_neg_cible / analysis ; define tx_min_cible_Evol_neg / analysis ; define tx_max_cible_Evol_neg / analysis ; define TOP_tx_max_cible_Evol_neg / computed ; define TOP_tx_min_cible_Evol_neg / computed ; define Evol_pos_cible / analysis ; define tx_min_cible_Evol_pos / analysis ; define tx_max_cible_Evol_pos / analysis ; define TOP_tx_max_cible_Evol_pos / computed ; define TOP_tx_min_cible_Evol_pos / computed ; compute top_tx_min_cible_CTT; top_tx_min_cible_CTT=tx_min_cible_CTT.SUM; endcomp; compute top_tx_max_cible_CTT; top_tx_max_cible_CTT =tx_max_cible_CTT.SUM; compute top_tx_min_cible_Evol_neg; top_tx_min_cible_Evol_neg=tx_min_cible_Evol_neg.SUM; endcomp; compute top_tx_max_cible_Evol_neg; top_tx_max_cible_Evol_neg =tx_max_cible_Evol_neg.SUM; compute top_tx_min_cible_Evol_pos; top_tx_min_cible_Evol_pos=tx_min_cible_Evol_pos.SUM; endcomp; compute top_tx_max_cible_Evol_pos; top_tx_max_cible_Evol_pos =tx_max_cible_Evol_pos.SUM; break after ZZ / summarize ; /*************************/ compute top_tx_max_cible_CTT; if nb_siz_cible.sum > 0 then do; top_tx_max_cible_CTT = ((CTT_cible.sum / nb_siz_cible.sum) + (1.96 * sqrt((CTT_cible.sum / nb_siz_cible.sum) * (1 - (CTT_cible.sum / nb_siz_cible.sum))) / nb_siz_cible.sum)) * 100; end; else top_tx_max_cible_CTT = .; endcomp; compute top_tx_min_cible_CTT ; if nb_siz_cible.sum > 0 then do; top_tx_min_cible_CTT = ((CTT_cible.sum / nb_siz_cible.sum) + (1.96 * sqrt((CTT_cible.sum / nb_siz_cible.sum) * (1 - (CTT_cible.sum / nb_siz_cible.sum))) / nb_siz_cible.sum)) * 100; end; else top_tx_min_cible_CTT = .; endcomp; /*****************/ compute top_tx_max_cible_Evol_neg; if nb_siz_cible.sum > 0 then do; top_tx_max_cible_Evol_neg = ((Evol_neg_cible.sum / nb_siz_cible.sum) + (1.96 * sqrt((Evol_neg_cible.sum / nb_siz_cible.sum) * (1 - (Evol_neg_cible.sum / nb_siz_cible.sum))) / nb_siz_cible.sum)) * 100; end; else top_tx_max_cible_Evol_neg = .; endcomp; compute top_tx_min_cible_Evol_neg; if nb_siz_cible.sum > 0 then do; top_tx_min_cible_Evol_neg = ((Evol_neg_cible.sum / nb_siz_cible.sum) + (1.96 * sqrt((Evol_neg_cible.sum / nb_siz_cible.sum) * (1 - (Evol_neg_cible.sum / nb_siz_cible.sum))) / nb_siz_cible.sum)) * 100; end; else top_tx_min_cible_Evol_neg = .; endcomp; /*******************************/ compute top_tx_max_cible_Evol_pos; if nb_siz_cible.sum > 0 then do; top_tx_max_cible_Evol_pos = ((Evol_pos_cible.sum / nb_siz_cible.sum) + (1.96 * sqrt((Evol_pos_cible.sum / nb_siz_cible.sum) * (1 - (Evol_pos_cible.sum / nb_siz_cible.sum))) / nb_siz_cible.sum)) * 100; end; else top_tx_max_cible_Evol_pos = .; endcomp; compute top_tx_min_cible_Evol_pos; if nb_siz_cible.sum > 0 then do; top_tx_min_cible_Evol_pos = ((Evol_pos_cible.sum / nb_siz_cible.sum) + (1.96 * sqrt((Evol_pos_cible.sum / nb_siz_cible.sum) * (1 - (Evol_pos_cible.sum / nb_siz_cible.sum))) / nb_siz_cible.sum)) * 100; end; else top_tx_min_cible_Evol_pos = .; endcomp; run; ods excel close;   ... View more

Thanks for your feedback for reasons of confidentiality at the moment I do not have the right to communicate this data, I will check with my hierarchy. while waiting I reproduced exactly the same report proc with a few columns and with the same characteristics as my real column (group, display, analysis, computed...) my columns. if that can help you see clearly and help me later.   ERROR: There are multiple COMPUTE statements for top_tx_min_cible_CTT ods excel file='..........xlsx' style=pearl; ods excel options (embedded_titles='none' FROZEN_ROWHEADERS='5' frozen_headers='yes' ); %macro calc_top_sum(var); compute top_tx_min_cible_&var; top_tx_min_cible_&var = tx_min_cible_&var..sum; endcomp; compute top_tx_max_cible_&var; top_tx_max_cible_&var = tx_max_cible_&var..sum; endcomp; %mend calc_top_sum; %macro calc_top_taux_tot(var); compute top_tx_max_cible_&var; if nb_siz_cible.sum > 0 then do; top_tx_max_cible_&var = ((&var._cible.sum / nb_siz_cible.sum) + (1.96 * sqrt((&var._cible.sum / nb_siz_cible.sum) * (1 - (&var._cible.sum / nb_siz_cible.sum))) / nb_siz_cible.sum)) * 100; end; else top_tx_max_cible_&var = .; endcomp; compute top_tx_min_cible_&var; if nb_siz_cible.sum > 0 then do; top_tx_min_cible_&var = ((&var._cible.sum / nb_siz_cible.sum) + (1.96 * sqrt((&var._cible.sum / nb_siz_cible.sum) * (1 - (&var._cible.sum / nb_siz_cible.sum))) / nb_siz_cible.sum)) * 100; end; else top_tx_min_cible_&var = .; endcomp; %mend calc_top_taux_tot; proc report data=TAB_ANALYSE ; column ("~" ZZ ) ("_blanc1_" nb_siz_cible) ("CC" CTT_cible tx_min_cible_CTT tx_max_cible_CTT top_tx_min_cible_CTT top_tx_max_cible_CTT ) ("BB" Evol_neg_cible tx_min_cible_Evol_neg tx_max_cible_Evol_neg TOP_tx_max_cible_Evol_neg TOP_tx_min_cible_Evol_neg) ("DD" Evol_pos_cible tx_min_cible_Evol_pos tx_max_cible_Evol_pos TOP_tx_max_cible_Evol_pos TOP_tx_min_cible_Evol_pos); define ZZ / group order=data ; define nb_siz_cible / analysis ; define CTT_cible / analysis ; define tx_min_cible_CTT / analysis ; define tx_max_cible_CTT / analysis ; define top_tx_min_cible_CTT / computed ; define top_tx_max_cible_CTT / computed ; define Evol_neg_cible / analysis ; define tx_min_cible_Evol_neg / analysis ; define tx_max_cible_Evol_neg / analysis ; define TOP_tx_max_cible_Evol_neg / computed ; define TOP_tx_min_cible_Evol_neg / computed ; define Evol_pos_cible / analysis ; define tx_min_cible_Evol_pos / analysis ; define tx_max_cible_Evol_pos / analysis ; define TOP_tx_max_cible_Evol_pos / computed ; define TOP_tx_min_cible_Evol_pos / computed ; %calc_top_sum(CTT); %calc_top_sum(Evol_neg); %calc_top_sum(Evol_pos); break after ZZ / summarize ; %calc_top_taux_tot(CTT); %calc_top_taux_tot(Evol_neg); %calc_top_taux_tot(Evol_pos); endcomp; run; ods excel close;     ... View more

  I have the same problem with that... with error ERROR: Invalid column specification in CALL DEFINE. I can't find the error /*SIGN_&var ===> display AND gaint_&var IS ANALYSIS */ %macro apply_style_sign(var); compute SIGN_&var; if SIGN_&var=1 and gaint_&var..sum >0 then do; call define( 'gaint_&var.sum' , "style", "style=[background=#C4D79B]" ); end; else if SIGN_&var=1 and gaint_&var..sum <=0 then do; call define( 'gaint_&var.sum' , "style", "style=[background=#FABF8F]" ); end; endcomp; %mend apply_style_sign;     ... View more

Hello, is there anyone who can help me?  I'm really stuck and I haven't been able to find the solution for almost 2 days 😥😥   ... View more

@Ksharp Thank you Ksharp for your reply. @Cynthia_sas Thank you Cyntia for the clarification. I forgot to mention that among my data there are also calculated columns. This solution works with display and analysis columns but I have difficulty making it work on a calculated column. Is there a trick to make this work? ... View more