Hello @PaigeMiller , thank you. I would remember that for the future request. This data is a made-up data, so some information was missed. I had omitted a column and 2 columns are a not numbers. Could you please take another look at the following SAS code. I have 2 errors which I have noted in /* */ in the SAS code. t1.Team has a mix of numbers and letters with numbers (example: TA01) and everything else are strings. That's how it was structured in the original tables. The want would contain Team, EventID, ID, Department, and Result. PROC SQL;
CREATE TABLE WORK.Have AS
SELECT DISTINCT t1.Team,
t2.EventID,
t2.ID,
t3.Department,
t4.Result
FROM AAA.Table1 t1, AAA.Table2 t2, AAA.Table3 t3, WORK.Table4 t4,
WHERE (t1.Key1 = t2.Key1 AND t1.Key1 = t3.Key1 AND t3.Key2 = t4.Key2 AND (t1.DT >= '1Oct2022:0:0:0'dt AND t1.Active = 'A');
QUIT;
proc transpose data=WORK.Have out=WORK.Have2 prefix=Result;
by ID Team;
var Result;
run;
/* ERROR: Data set WORK.Have is not sorted in ascending sequence. The current BY group has Team = TA01 and the next BY group has Team =
2900. */
data WORK.Have3;
set WORK.Have2;
array r Result:;
number_yes=0;
number_no=0;
number_maybe=0;
do i=1 to dim(r);
number_yes=number_yes+(upcase(r(i))='YES');
number_no=number_no+(upcase(r(i))='NO');
number_maybe=number_maybe+(upcase(r(i))='MAYBE');
end;
drop i;
run;
data want;
merge WORK.Have WORK.Have3(keep=number: ID Team);
/* ERROR: The variable Team in the DROP, KEEP, or RENAME list has never been referenced. */
by ID Team;
if number_yes>1 or (number_yes=1 and number_no=1) or (number_yes=1 and number_maybe=1);
drop number:;
run;
... View more