Hello,    Yes, median is not resolved that is the problem here. Now I have updated and it is working.   Thank you all! ... View more

Here is the log: 217 *************** 218 **** Stats **** 219 **************; 220 ** Get descriptive stats and create macro to repeat for every variable; 221 %macro getstats(out=, var1=, var2=, nn=, mm=); 222 %if 0=%length(&nn) %then %do; 223 %global seq ; 224 %let seq=%eval(&seq+1); 225 %let nn=&seq; 226 %let mm=&seq-0.5; 227 %end; 228 %if 0=%length(&out) %then %let out=out&nn ; 229 230 proc means data=adsl; 231 var &var1.; 232 class trtpn; 233 output out=step1 n=count mean=mn std=std median=mdn q1=qtr1 q3=qtr3 min=mini max=maxi; 234 run; 235 236 data step2; 237 set step1(where=(trtpn ne .)); 238 if count ne . then cnt=strip(put(count,8.)); 239 else cnt=''; 240 if mn ne . then mnsd=strip(put(mn,8.));*||" ("||strip(put(std,8.1))||")"; 241 else mnsd=''; 242 if mdn ne . then mdian=strip(put(mdn,8.)); 243 else mdian=''; 244 if qtr3 ne . then qtrs=strip(put(qtr1,8.))||", "||strip(put(qtr3,8.)); 245 else qtrs=''; 246 if mini ne . and maxi ne . then minmax=strip(put(mini,8.))||", "||strip(put(maxi,8.)); 247 else minmax=''; 248 if std ne . then stdev=strip(put(std,8.)); 249 else stdev=''; 250 keep trtpn cnt mnsd mdian qtrs minmax stdev; 251 run; 252 253 proc transpose data=step2 out=step3; 254 var cnt mnsd mdian qtrs minmax stdev; 255 id trtpn; 256 run; 257 258 data step4; 259 length v1 v2 v3 v4 $200.; 260 set step3; 261 262 if _NAME_ eq "cnt" then v1="~{nbspace 5}N"; 263 else if _NAME_ eq "mnsd" then v1="~{nbspace 5}MEAN"; 264 else if _NAME_ eq "mdian" then v1="~{nbspace 5}MEDIAN"; 265 else if _NAME_ eq "qtrs" then v1="~{nbspace 5}Q1, Q3"; 266 else if _NAME_ eq "minmax" then v1="~{nbspace 5}MIN, MAX"; 267 else if _NAME_ eq "stdev" then v1="~{nbspace 5}STANDARD DEVIATION"; 268 269 v2=_1; 270 v3=_2; 271 v4=_9; 272 273 ord=&nn.; 274 275 keep ord v1 v2 v3 v4; 276 run; 277 278 data dummy; 279 v1="&var2"; 280 ord=&mm.; 281 run; 282 283 data &out.; 284 set step4 dummy; 285 by ord; 286 run; 287 %mend getstats; 288 289 ************ 290 ** counts ** 291 ***********; 292 ** Get counts and create macro to repeat for every variable; 293 %macro getnum(out=, var1=, var2=, nn=); 294 %if 0=%length(&nn) %then %do; 295 %global seq ; 296 %let seq=%eval(&seq+1); 297 %let nn=&seq; 298 %end; 299 %if 0=%length(&out) %then %let out=out&nn ; 300 301 proc sql; 302 create table stp1 as 303 select distinct trtpn, count(distinct usubjid) as n, 304 case when trtpn=1 then strip(put(calculated n, 3.))||" ("||strip(put((calculated 304 ! n/&n1.)*100, 5.1))||")" 305 when trtpn=2 then strip(put(calculated n, 3.))||" ("||strip(put((calculated 305 ! n/&n2.)*100, 5.1))||")" 306 when trtpn=9 then strip(put(calculated n, 3.))||" ("||strip(put((calculated 306 ! n/&n3.)*100, 5.1))||")" 307 else "" 308 end as numm 309 from adsl1 310 where &var1. 311 group by trtpn; 312 quit; 313 314 proc transpose data=stp1 out=stp2; 315 var numm; 316 id trtpn; 317 run; 318 319 data &out.; 320 length v1 v2 v3 v4 $200.; 321 set stp2; 322 323 v1=&var2.; 324 v2=_1; 325 v3=_2; 326 v4=_9; 327 328 ord=&nn.; 329 330 keep ord v1-v4; 331 run; 332 %mend; 333 334 %let seq=0; 421 ** Median calculation for counts category untill a variable created in ADSL; 422 proc sql noprint; 423 select v4 into :median from out59 where v1 eq '~{nbspace 5}MEDIAN'; NOTE: No rows were selected. 424 quit; NOTE: PROCEDURE SQL used (Total process time): real time 0.01 seconds user cpu time 0.00 seconds system cpu time 0.01 seconds memory 5684.12k OS Memory 29948.00k Timestamp 01/04/2021 02:36:14 PM Step Count 205 Switch Count 0 WARNING: Apparent symbolic reference MEDIAN not resolved. 425 %put &median.; &median. 426 ** PSA BY MEDIAN (XXXX); 427 %getnum(var1=PSA ne ., var2=%str("PSA BY MEDIAN (&median.)"), nn=); WARNING: Apparent symbolic reference MEDIAN not resolved. NOTE: Table WORK.STP1 created, with 3 rows and 3 columns. NOTE: PROCEDURE SQL used (Total process time): real time 0.02 seconds user cpu time 0.00 seconds system cpu time 0.00 seconds memory 5809.00k OS Memory 29948.00k Timestamp 01/04/2021 02:36:14 PM Step Count 206 Switch Count 0 NOTE: There were 3 observations read from the data set WORK.STP1. NOTE: The data set WORK.STP2 has 1 observations and 4 variables. NOTE: PROCEDURE TRANSPOSE used (Total process time): real time 0.02 seconds user cpu time 0.00 seconds system cpu time 0.01 seconds memory 2836.21k OS Memory 26876.00k Timestamp 01/04/2021 02:36:14 PM Step Count 207 Switch Count 0 WARNING: Apparent symbolic reference MEDIAN not resolved. NOTE: There were 1 observations read from the data set WORK.STP2. NOTE: The data set WORK.OUT61 has 1 observations and 5 variables. NOTE: DATA statement used (Total process time): real time 0.01 seconds user cpu time 0.00 seconds system cpu time 0.01 seconds memory 1305.84k OS Memory 25340.00k Timestamp 01/04/2021 02:36:14 PM Step Count 208 Switch Count 0 428 options mprint; 429 %getnum(var1=PSA < &median., var2=%str("~{nbspace 5}< MEDIAN"), nn=); WARNING: Apparent symbolic reference MEDIAN not resolved. MPRINT(GETNUM): proc sql; WARNING: Apparent symbolic reference MEDIAN not resolved. NOTE 138-205: Line generated by the macro variable "VAR1". 1 PSA < &median. - 22 ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, a numeric constant, a datetime constant, a missing value, BTRIM, INPUT, PUT, SUBSTRING, USER. WARNING: Apparent symbolic reference MEDIAN not resolved. NOTE 137-205: Line generated by the invoked macro "GETNUM". 2 3.))||" ("||strip(put((calculated n/&n2.)*100, 5.1))||")" when trtpn=9 then 2 ! strip(put(calculated n, 3.))||" ("||strip(put((calculated n/&n3.)*100, 5.1))||")" 2 ! else "" end as numm from adsl1 where &var1. group by trtpn; -- 22 ERROR 22-322: Syntax error, expecting one of the following: GROUP, ORDER. NOTE: Line generated by the invoked macro "GETNUM". 2 3.))||" ("||strip(put((calculated n/&n2.)*100, 5.1))||")" when trtpn=9 then 2 ! strip(put(calculated n, 3.))||" ("||strip(put((calculated n/&n3.)*100, 5.1))||")" 2 ! else "" end as numm from adsl1 where &var1. group by trtpn; -- 76 ERROR 76-322: Syntax error, statement will be ignored. MPRINT(GETNUM): create table stp1 as select distinct trtpn, count(distinct usubjid) as n, case when trtpn=1 then strip(put(calculated n, 3.))||" ("||strip(put((calculated n/ 3)*100, 5.1))||")" when trtpn=2 then strip(put(calculated n, 3.))||" ("||strip(put((calculated n/ 4)*100, 5.1))||")" when trtpn=9 then strip(put(calculated n, 3.))||" ("||strip(put((calculated n/ 7)*100, 5.1))||")" else "" end as numm from adsl1 where PSA < & group by trtpn; MPRINT(GETNUM): quit; NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE SQL used (Total process time): real time 0.08 seconds user cpu time 0.01 seconds system cpu time 0.04 seconds memory 74.84k OS Memory 24308.00k Timestamp 01/04/2021 02:36:14 PM Step Count 209 Switch Count 0 MPRINT(GETNUM): proc transpose data=stp1 out=stp2; MPRINT(GETNUM): var numm; MPRINT(GETNUM): id trtpn; MPRINT(GETNUM): run; NOTE: There were 3 observations read from the data set WORK.STP1. NOTE: The data set WORK.STP2 has 1 observations and 4 variables. NOTE: PROCEDURE TRANSPOSE used (Total process time): real time 0.02 seconds user cpu time 0.00 seconds system cpu time 0.01 seconds memory 2836.21k OS Memory 26876.00k Timestamp 01/04/2021 02:36:14 PM Step Count 210 Switch Count 0 MPRINT(GETNUM): data out62; MPRINT(GETNUM): length v1 v2 v3 v4 $200.; MPRINT(GETNUM): set stp2; MPRINT(GETNUM): v1="~{nbspace 5}< MEDIAN"; MPRINT(GETNUM): v2=_1; MPRINT(GETNUM): v3=_2; MPRINT(GETNUM): v4=_9; MPRINT(GETNUM): ord=62; MPRINT(GETNUM): keep ord v1-v4; MPRINT(GETNUM): run; NOTE: There were 1 observations read from the data set WORK.STP2. NOTE: The data set WORK.OUT62 has 1 observations and 5 variables. NOTE: DATA statement used (Total process time): real time 0.04 seconds user cpu time 0.01 seconds system cpu time 0.01 seconds memory 1305.71k OS Memory 25340.00k Timestamp 01/04/2021 02:36:14 PM Step Count 211 Switch Count 0   ... View more

Okay, got it. Thank you so much. But I have one more question. Can I use &median. like below? But it looks like it is not working properly? I mean &median. value is not taking in below var1 macro variable.   ** Median calculation for counts category untill a variable created in ADSL; proc sql noprint; select v4 into :median from out59 where v1 eq '~{nbspace 5}MEDIAN'; quit; %put &median.; ** PSA BY MEDIAN (XXXX); %getnum(var1=PSA ne ., var2=%str("PSA BY MEDIAN (&median.)"), nn=); %getnum(var1=PSA < &median., var2=%str("~{nbspace 5}< MEDIAN"), nn=); %getnum(var1=PSA >= &median., var2=%str("~{nbspace 5}>= MEDIAN"), nn=); Sorry for the confusion. ... View more

Yes, because of special characters I am using %str, %unquote. I have given my whole code in one of my replies. ... View more

Below is the macro I have written for counts, and callings ** Get counts and create macro to repeat for every variable; %macro getnum(out=, var1=, var2=, nn=); %if 0=%length(&nn) %then %do; %global seq ; %let seq=%eval(&seq+1); %let nn=&seq; %end; %if 0=%length(&out) %then %let out=out&nn ; proc sql; create table stp1 as select distinct trtpn, count(distinct usubjid) as n, case when trtpn=1 then strip(put(calculated n, 3.))||" ("||strip(put((calculated n/&n1.)*100, 5.1))||")" when trtpn=2 then strip(put(calculated n, 3.))||" ("||strip(put((calculated n/&n2.)*100, 5.1))||")" when trtpn=9 then strip(put(calculated n, 3.))||" ("||strip(put((calculated n/&n3.)*100, 5.1))||")" else "" end as numm from adsl1 where &var1. group by trtpn; quit; proc transpose data=stp1 out=stp2; var numm; id trtpn; run; data &out.; length v1 v2 v3 v4 $200.; set stp2; v1=&var2.; v2=_1; v3=_2; v4=_9; ord=&nn.; keep ord v1-v4; run; %mend; %getstats(var1=tidist, var2=%str(TIME FROM INITIAL DIAGNOSIS TO INITIATION OF STUDY THERAPY (YEARS)), nn=, mm=); %getnum(var1=TIDISTC ne '', var2=%unquote("TIME FROM INITIAL DIAGNOSIS TO INITIATION OF STUDY THERAPY (%)"), nn=); %getnum(var1=PSA ne ., var2=%str("PSA BY MEDIAN (&median.)"), nn=); ... View more

Okay, if I create macro variable inside quotes as "&var2" How can I give special characters such as %, (, ). etc. So I have created &var2 like above. ... View more

I not getting actual macro variable value in quotations, I am getting as "PSA BY MEDIAN (&median.)". ... View more

Thank you so much for your reply! Can we also assign output dataset name as out&nn. while calling macro? Like below? %let seq=0; %getnum(out=out&nn., var1=TIDRANDC ne '', var2='TIME FROM INITIAL DIAGNOSIS TO RANDOMIZATION (%)', nn=); %getnum(out=out&nn., var1=TIDRANDC eq '< 1 years', var2='< 1 YEARS', nn=); %getnum(out=out&nn., var1=TIDRANDC eq '>= 1 years', var2='>= 1 YEARS', nn=); %getnum(out=out&nn., var1=TIDRANDC eq '', var2='NOT REPORTED', nn=); ... View more

** Get counts and create macro to repeat for every variable; %macro getnum(out=, var1=, var2=, nn=); proc sql; create table stp1 as select distinct trtpn, count(distinct usubjid) as n, case when trtpn=1 then strip(put(calculated n, 3.))||" ("||strip(put((calculated n/&n1.)*100, 5.1))||")" when trtpn=2 then strip(put(calculated n, 3.))||" ("||strip(put((calculated n/&n2.)*100, 5.1))||")" when trtpn=9 then strip(put(calculated n, 3.))||" ("||strip(put((calculated n/&n3.)*100, 5.1))||")" else "" end as numm from adsl1 where &var1. group by trtpn; quit; proc transpose data=stp1 out=stp2; var numm; id trtpn; run; data &out.; length v1 v2 v3 v4 $200.; set stp2; v1=&var2.; v2=_1; v3=_2; v4=_9; ord=&nn.; keep ord v1-v4; run; %mend; ** Call macro to get final output; ** TIME FROM INITIAL DIAGNOSIS TO RANDOMIZATION (%); %getnum(out=out2, var1=TIDRANDC ne '', var2=%str("TIME FROM INITIAL DIAGNOSIS TO RANDOMIZATION (%)"), nn=2); %getnum(out=out3, var1=TIDRANDC eq '< 1 years', var2=%str("< 1 YEARS"), nn=3); %getnum(out=out4, var1=TIDRANDC eq '>= 1 years', var2=%str(">= 1 YEARS"), nn=4); %getnum(out=out5, var1=TIDRANDC eq '', var2=%str("NOT REPORTED"), nn=5); ... View more

Thank you so much! ... View more

Okay,    I am creating a macro for row counts, so I am giving order number manually like below. But I would like to give those numbers similar to line number instead of changing every time when I add new rows? I am using SAS9.4.     Thanks, ... View more

I would like to use line number as sequence, is it possible?   Thanks,   ... View more