Thank you @Tom. I got the values in the table now. One more question, after this step I used PROC SQL to calculate subjects with an event but when I combine and transpose it that number is sorted according to alphabets but I want the number of subjects with event at the top like shown in this picture. when I first tried my code it was working fine but after that it is not giving me same result not sure why. I would really appreciate your help. Here is my code. PROC IMPORT DATAFILE="C:\Users\AE.xls" OUT=A DBMS=XLS; GETNAMES=YES; RUN; *SELECT RECORDS AND VARIABLES FROM ADAE; DATA A1; SET A; IF SAFFL='Y'; RUN; DATA A2; SET A1; IF INDEX(UPCASE(TRTP), "ACTIVE DRUG A") >0 THEN DO; TRT = "A"; ORD = 1; END; IF INDEX(UPCASE(TRTP), "PLACEBO") >0 THEN DO; TRT = "B"; ORD = 2; END; KEEP USUBJID AEBODSYS AEDECOD AETOXGR TRT ORD; RUN; *CALCULATING 'N' COUNT; PROC SQL NOPRINT; SELECT COUNT (DISTINCT USUBJID) INTO : N1 -:N2 FROM A2 GROUP BY ORD ORDER BY ORD; QUIT; %PUT &N1 &N2; *COUNT OF SUBJECTS WITH EVENTS; PROC SQL NOPRINT; CREATE TABLE ANY AS SELECT TRT,COUNT (DISTINCT USUBJID) AS N, "NUMBER OF SUBJECTS WITH AN EVENT" AS AEBODSYS LENGTH=200 FROM A2 GROUP BY TRT; CREATE TABLE BODSYS AS SELECT TRT,AEBODSYS, COUNT (DISTINCT USUBJID) AS N FROM A2 GROUP BY TRT, AEBODSYS; CREATE TABLE DECOD AS SELECT TRT,AEBODSYS,AEDECOD, COUNT (DISTINCT USUBJID) AS N FROM A2 GROUP BY TRT, AEBODSYS,AEDECOD; QUIT; DATA COMBINE; SET ANY BODSYS DECOD; RUN; *TRANSPOSE THE DATA; PROC SORT; BY AEBODSYS AEDECOD TRT; RUN; PROC TRANSPOSE DATA=COMBINE OUT=COMBINE1; ID TRT; BY AEBODSYS AEDECOD; RUN; *CALCULATE THE PERCENTAGES; DATA FINAL; SET COMBINE1; LENGTH DRUGA PLACEBO $100.; IF A = . THEN DRUGA = "0 (0)"; ELSE IF A = &N1 THEN DRUGA = PUT (A, 3.) || "(100%)"; ELSE DRUGA = PUT (A, 3.) || "(" || PUT(A/ &N1 *100, 4.1) || ")"; IF B = . THEN PLACEBO = "0 (0)"; ELSE IF B = &N2 THEN PLACEBO = PUT (B, 3.) || "(100%)"; ELSE PLACEBO= PUT (B, 3.) || "(" || PUT(B/ &N2 *100, 4.1) || ")"; IF AEDECOD EQ " " AND AEBODSYS NE " " THEN AEBODSYS1 = AEBODSYS; ELSE AEBODSYS1 = " "||AEDECOD; DROP A B ; RUN; PROC REPORT DATA=FINAL HEADLINE HEADSKIP SPLIT='*'; COLUMN AEBODSYS1 PLACEBO DRUGA; DEFINE AEBODSYS1/DISPLAY "System Organ Class(%)*Preferred Term (%)"; DEFINE PLACEBO/DISPLAY "PLACEBO* (N=&N2)"; DEFINE DRUGA/DISPLAY "TREATMENT GROUP A* (N=&N1)"; RUN;
... View more