OK. Sorry to take so long, but we were busy this week at work. What I've done is written a program that will remove all of the weeks with too many missing tests. If there are more than 3 missing tests, I remove that week for that ID. If a score is 0, I consider that to be missing. In other words, 0 is missing. I hope I've understood that correctly.
Assuming that 0 is the same as missing, then not only are 'PP01','PP04','PP09','PP10' missing for Week 2 but also 'PP08'.
The final result is a dataset with all weeks that are "missing" removed.
Here is the program:
/*Week 2, 'PP01','PP04','PP09','PP10' is missing that means symptoms average for week 2 should be missing*/
/*out of 5 questions only 1 question was answered*/
/*also 'PP08' is missing because all the scores are 0*/
DATA TEST ;
INFILE DATALINES DLM="09"X DSD;
INPUT $ TESTCD$ TEST:$13-42 SCORE DAY Period $;
datalines
;
M21136 PP01 SeverityofItch 6 3 Week 1
M21136 PP01 SeverityofItch 6 5 Week 1
M21136 PP01 SeverityofItch 6 6 Week 1
M21136 PP01 SeverityofItch 7 1 Week 1
M21136 PP01 SeverityofItch 7 2 Week 1
M21136 PP01 SeverityofItch 7 4 Week 1
M21136 PP02 SeverityofDryness 5 2 Week 1
M21136 PP02 SeverityofDryness 6 4 Week 1
M21136 PP02 SeverityofDryness 6 6 Week 1
M21136 PP02 SeverityofDryness 7 1 Week 1
M21136 PP02 SeverityofDryness 7 3 Week 1
M21136 PP02 SeverityofDryness 7 5 Week 1
M21136 PP03 SeverityofCracking 5 4 Week 1
M21136 PP03 SeverityofCracking 6 1 Week 1
M21136 PP03 SeverityofCracking 6 2 Week 1
M21136 PP03 SeverityofCracking 6 3 Week 1
M21136 PP03 SeverityofCracking 6 6 Week 1
M21136 PP03 SeverityofCracking 7 5 Week 1
M21136 PP04 SeverityofSkin Tightness 4 4 Week 1
M21136 PP04 SeverityofSkin Tightness 6 1 Week 1
M21136 PP04 SeverityofSkin Tightness 6 6 Week 1
M21136 PP04 SeverityofSkin Tightness 7 2 Week 1
M21136 PP04 SeverityofSkin Tightness 7 3 Week 1
M21136 PP04 SeverityofSkin Tightness 8 5 Week 1
M21136 PP05 SeverityofScaling 5 4 Week 1
M21136 PP05 SeverityofScaling 6 1 Week 1
M21136 PP05 SeverityofScaling 6 2 Week 1
M21136 PP05 SeverityofScaling 6 3 Week 1
M21136 PP05 SeverityofScaling 6 6 Week 1
M21136 PP05 SeverityofScaling 7 5 Week 1
M21136 PP06 SeverityofShredding or Flaking 5 3 Week 1
M21136 PP06 SeverityofShredding or Flaking 5 4 Week 1
M21136 PP06 SeverityofShredding or Flaking 6 1 Week 1
M21136 PP06 SeverityofShredding or Flaking 6 6 Week 1
M21136 PP06 SeverityofShredding or Flaking 7 2 Week 1
M21136 PP06 SeverityofShredding or Flaking 7 5 Week 1
M21136 PP07 SeverityofRedness 5 1 Week 1
M21136 PP07 SeverityofRedness 5 4 Week 1
M21136 PP07 SeverityofRedness 6 2 Week 1
M21136 PP07 SeverityofRedness 6 3 Week 1
M21136 PP07 SeverityofRedness 6 6 Week 1
M21136 PP07 SeverityofRedness 7 5 Week 1
M21136 PP08 SeverityofBleeding 0 1 Week 1
M21136 PP08 SeverityofBleeding 0 3 Week 1
M21136 PP08 SeverityofBleeding 1 2 Week 1
M21136 PP08 SeverityofBleeding 1 6 Week 1
M21136 PP08 SeverityofBleeding 5 4 Week 1
M21136 PP08 SeverityofBleeding 7 5 Week 1
M21136 PP09 SeverityofBurning 5 4 Week 1
M21136 PP09 SeverityofBurning 6 1 Week 1
M21136 PP09 SeverityofBurning 6 5 Week 1
M21136 PP09 SeverityofBurning 6 6 Week 1
M21136 PP09 SeverityofBurning 7 2 Week 1
M21136 PP09 SeverityofBurning 7 3 Week 1
M21136 PP10 SeverityofStinging 5 4 Week 1
M21136 PP10 SeverityofStinging 6 1 Week 1
M21136 PP10 SeverityofStinging 6 2 Week 1
M21136 PP10 SeverityofStinging 6 6 Week 1
M21136 PP10 SeverityofStinging 7 3 Week 1
M21136 PP10 SeverityofStinging 7 5 Week 1
M21136 PP11 SeverityofPain from Psoriasis 5 4 Week 1
M21136 PP11 SeverityofPain from Psoriasis 5 6 Week 1
M21136 PP11 SeverityofPain from Psoriasis 6 1 Week 1
M21136 PP11 SeverityofPain from Psoriasis 6 5 Week 1
M21136 PP11 SeverityofPain from Psoriasis 7 2 Week 1
M21136 PP11 SeverityofPain from Psoriasis 7 3 Week 1
M21136 PP02 SeverityofDryness 3 12 Week 2
M21136 PP02 SeverityofDryness 4 7 Week 2
M21136 PP02 SeverityofDryness 4 10 Week 2
M21136 PP02 SeverityofDryness 4 11 Week 2
M21136 PP02 SeverityofDryness 4 13 Week 2
M21136 PP02 SeverityofDryness 5 9 Week 2
M21136 PP02 SeverityofDryness 6 8 Week 2
M21136 PP03 SeverityofCracking 3 11 Week 2
M21136 PP03 SeverityofCracking 4 12 Week 2
M21136 PP03 SeverityofCracking 4 13 Week 2
M21136 PP03 SeverityofCracking 5 7 Week 2
M21136 PP03 SeverityofCracking 5 9 Week 2
M21136 PP03 SeverityofCracking 5 10 Week 2
M21136 PP03 SeverityofCracking 7 8 Week 2
M21136 PP05 SeverityofScaling 3 11 Week 2
M21136 PP05 SeverityofScaling 4 7 Week 2
M21136 PP05 SeverityofScaling 4 12 Week 2
M21136 PP05 SeverityofScaling 4 13 Week 2
M21136 PP05 SeverityofScaling 5 9 Week 2
M21136 PP05 SeverityofScaling 6 10 Week 2
M21136 PP05 SeverityofScaling 7 8 Week 2
M21136 PP06 SeverityofShredding or Flaking 4 10 Week 2
M21136 PP06 SeverityofShredding or Flaking 4 11 Week 2
M21136 PP06 SeverityofShredding or Flaking 4 12 Week 2
M21136 PP06 SeverityofShredding or Flaking 4 13 Week 2
M21136 PP06 SeverityofShredding or Flaking 5 7 Week 2
M21136 PP06 SeverityofShredding or Flaking 5 9 Week 2
M21136 PP06 SeverityofShredding or Flaking 6 8 Week 2
M21136 PP07 SeverityofRedness 3 11 Week 2
M21136 PP07 SeverityofRedness 3 13 Week 2
M21136 PP07 SeverityofRedness 4 7 Week 2
M21136 PP07 SeverityofRedness 4 12 Week 2
M21136 PP07 SeverityofRedness 5 9 Week 2
M21136 PP07 SeverityofRedness 5 10 Week 2
M21136 PP07 SeverityofRedness 7 8 Week 2
M21136 PP08 SeverityofBleeding 0 7 Week 2
M21136 PP08 SeverityofBleeding 0 8 Week 2
M21136 PP08 SeverityofBleeding 0 9 Week 2
M21136 PP08 SeverityofBleeding 0 10 Week 2
M21136 PP08 SeverityofBleeding 0 11 Week 2
M21136 PP08 SeverityofBleeding 0 12 Week 2
M21136 PP08 SeverityofBleeding 0 13 Week 2
M21136 PP11 SeverityofPain from Psoriasis 3 10 Week 2
M21136 PP11 SeverityofPain from Psoriasis 4 7 Week 2
M21136 PP11 SeverityofPain from Psoriasis 4 9 Week 2
M21136 PP11 SeverityofPain from Psoriasis 4 11 Week 2
M21136 PP11 SeverityofPain from Psoriasis 4 13 Week 2
M21136 PP11 SeverityofPain from Psoriasis 5 12 Week 2
M21136 PP11 SeverityofPain from Psoriasis 6 8 Week 2
;
RUN;
PROC SQL NOPRINT;
SELECT COUNT(DISTINCT TestCd)
INTO : Nbr_of_Tests TRIMMED
FROM TEST
;
QUIT;
%PUT NOTE: &=Nbr_of_Tests;
DATA Valid_Weeks (KEEP=ID Period Missing_Tests Nbr_of_Tests_Taken);
RETAIN PP01 - PP&Nbr_of_Tests 0;
ARRAY PP_Cnt {*} PP01 - PP&Nbr_of_Tests _TEMPORARY_;
SET TEST;
BY ID Period NOTSORTED;
IF UPCASE(TestCD) = 'PP01' AND
Score > 0 THEN
PP01 = 1;
ELSE
IF UPCASE(TestCD) = 'PP02' AND
Score > 0 THEN
PP02 = 1;
ELSE
IF UPCASE(TestCD) = 'PP03' AND
Score > 0 THEN
PP03 = 1;
ELSE
IF UPCASE(TestCD) = 'PP04' AND
Score > 0 THEN
PP04 = 1;
ELSE
IF UPCASE(TestCD) = 'PP05' AND
Score > 0 THEN
PP05 = 1;
ELSE
IF UPCASE(TestCD) = 'PP06' AND
Score > 0 THEN
PP06 = 1;
ELSE
IF UPCASE(TestCD) = 'PP07' AND
Score > 0 THEN
PP07 = 1;
ELSE
IF UPCASE(TestCD) = 'PP08' AND
Score > 0 THEN
PP08 = 1;
ELSE
IF UPCASE(TestCD) = 'PP09' AND
Score > 0 THEN
PP09 = 1;
ELSE
IF UPCASE(TestCD) = 'PP10' AND
Score > 0 THEN
PP10 = 1;
ELSE
IF UPCASE(TestCD) = 'PP11' AND
Score > 0 THEN
PP11 = 1;
IF LAST.Period THEN
DO;
Nbr_of_Tests_Taken = SUM(of PP01 - PP11);
Missing_Tests = &Nbr_of_Tests - Nbr_of_Tests_Taken;
DO i = 1 TO &Nbr_of_Tests;
PP_Cnt {i} = 0;
END;
IF Missing_Tests > 3 THEN
DELETE;
ELSE
OUTPUT;
END;
ELSE
DO;
DELETE;
END;
RUN;
PROC SQL;
CREATE TABLE Test_Not_Missing AS
SELECT *
FROM TEST
WHERE CATS(ID, Period) IN (
SELECT CATS(ID, Period)
FROM Valid_Weeks);
;
QUIT;
I hope this is what you need,
Jim
... View more
