INDEX function not working

[billi_billi]

Hello Everyone,

Just starting coding so maybe this is very basic question. I used INDEX function, but not getting the desired results in the table and also I am not getting any error in the log. Can anyone please help me and tell me what is wrong in my code. My code is below and the data that I used is attached. When I create dataset A2, I do not get any value for variable TRT and ORD.

TIA

*IMPORT THE DATA;
PROC IMPORT DATAFILE="C:\Users\Data.xls"
OUT=A
DBMS=XLS;
GETNAMES=YES;
RUN;

*SELECT RECORDS AND VARIABLES FROM data;
DATA A1;
SET A;
IF SAFFL='Y';
RUN;

DATA A2;
SET A1;

IF INDEX(TRTP, "ACTIVE DRUG A") >0 THEN DO; TRT = "A"; ORD = 1; END;
IF INDEX(TRTP, "PLACEBO") >0 THEN DO; TRT = "B"; ORD = 2; END;

KEEP USUBJID TRT ORD;
RUN;

[Tom · Accepted solution]

I don't see any values of PLACEBO in that variable, at least as the XLS file is render by this site.

I see plenty of values of Placebo, but your code is not looking for that string, just the string with all uppercase letters.

Try:

IF INDEX(upcase(TRTP), "PLACEBO") >0 THEN DO; 

[billi_billi]

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;

[billi_billi]

Please disregard my second question I figured it out. Thank you