Solved: Re: clinical trail scoring

vnreddy · Posted 07-30-2021 08:53 AM

Hi,

could someone help me with below example, i have attached a test data and i have created period from DAY variable, i want to see the data in weeks: week 1, week 2

The PP is an 11-item subject-reported instrument that assesses severity of symptoms and subject observed signs commonly associated in plaque psoriasis. It has been shown to be reliable and valid in measuring symptoms and signs of subjects with moderate-to-severe plaque psoriasis in the clinic and has strong psychometric properties in assessing treatment effects in clinical trials. Subjects are asked to rate the severity of each item on an 11-point Numeric Rating Scale ranging from 0 (absent) to 10 (worst imaginable).

A symptom score will be derived by averaging the 5 questions (i.e., itch, pain, stinging, burning, skin tightness) included in the symptom score and multiplying by 10. To obtain a symptom score on a given day, responses to at least 2 of the 5 questions must be available. If more than 3 questions are missing, the symptom score is considered missing.

A sign score will be derived by averaging the 6 questions (i.e., skin dryness, cracking, scaling, shedding or flaking, redness, bleeding) included in the sign score and multiplying by 10. Responses to at least 3 of the 6 questions must be available in order to obtain a sign score for a given day. If more than 3 questions are missing, the sign score is considered missing.

Both symptom and sign scores range from 0 to 100, where 0 represents the least severe symptom/sign and 100 the most severe.

A total PP score with a range of 0 to 100 will be derived from taking the average of the symptom and sign scores.

Trial administered the PP with a 24-hour recall period on a daily basis. To calculate the scores at each visit, the daily scores with 24-hour recall periods over the prior 7 days will be used and the average score to each of the 11 questions will be used as the score at that visit. In case missing data arise during the 7 days prior to the visit, daily scores of at least 4 days out of the 7 can be used. If greater than 3 scores are missing, the average score will be missing. The baseline PP scores are calculated based on the daily diary collected data during the screening period.

jimbarbour · Posted 08-05-2021 06:36 PM

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 solution in original post

vnreddy · Posted 07-30-2021 10:38 AM

could someone help me here .

Symptom	PP01, PP04, PP09, PP10, PP11
Sign	PP02, PP03, PP05, PP06, PP07, PP08

jimbarbour · Posted 07-30-2021 12:57 PM

Would it be possible for your to post your data as a SAS program? Something like this:

DATA  Have;
  INFILE DATALINES DLM='09'X DSD MISSOVER;
  INPUT  x1 x2 x3 ... (etc.);
DATALINES;
1	2	3
1	2	3
1	2	3
1	2	3
;
RUN;

That way I can try working with it.

Jim

vnreddy · Posted 07-30-2021 01:16 PM

Hi Jim,

Could you please use this with the excel file that i have attached.

PROC IMPORT OUT=WORK.sample
DATAFILE="C:\Users\Desktop\test.xlsx"
DBMS=EXCEL REPLACE;
RANGE="Sheet1$";
GETNAMES=YES;
MIXED=YES;
SCANTEXT=YES;
USEDATE=YES;
SCANTIME=YES;
run;

ballardw · Posted 07-30-2021 02:09 PM

Many users here don't want to download Excel files because of virus potential, others have such things blocked by security software. Also if you give us Excel we have to create a SAS data set and due to the non-existent constraints on Excel data cells the result we end up with may not have variables of the same type (numeric or character) and even values.

jimbarbour · Posted 07-30-2021 02:43 PM

Hi, @vnreddy,

I do know how to do a Proc Import, and I know how to use the XLSX engine to directly read Excel. However, if there were a virus, which can happen, it would spread to my system. Probably you have good anti-virus software on your system, but hackers keep getting better and better. And, as @ballardw says, Excel can be a bit odd sometimes. We won't necessarily get identical data due to Installation settings, user defined Options, etc.

You can export to .csv or tab delimited text and put it as DATALINES in a SAS program. This should be very fast and easy to do, and then we are guaranteed no one has snuck in a virus and that the data is identical.

When you post a program, be sure to use the SAS icon:

Jim

vnreddy · Posted 07-30-2021 04:17 PM

Hi @jimbarbour

Sorry for the excel file, below is the datalines I have used as per your suggestion:

DATA TEST ;
INFILE DATALINES DLM="09"X DSD;
input ID $	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	PP01	SeverityofItch	3	11	Week 2
M21136	PP01	SeverityofItch	4	7	Week 2
M21136	PP01	SeverityofItch	4	9	Week 2
M21136	PP01	SeverityofItch	4	12	Week 2
M21136	PP01	SeverityofItch	4	13	Week 2
M21136	PP01	SeverityofItch	5	10	Week 2
M21136	PP01	SeverityofItch	7	8	Week 2
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	PP04	SeverityofSkin Tightness	4	9	Week 2
M21136	PP04	SeverityofSkin Tightness	4	11	Week 2
M21136	PP04	SeverityofSkin Tightness	4	12	Week 2
M21136	PP04	SeverityofSkin Tightness	4	13	Week 2
M21136	PP04	SeverityofSkin Tightness	5	7	Week 2
M21136	PP04	SeverityofSkin Tightness	5	10	Week 2
M21136	PP04	SeverityofSkin Tightness	6	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	PP09	SeverityofBurning	1	10	Week 2
M21136	PP09	SeverityofBurning	4	9	Week 2
M21136	PP09	SeverityofBurning	4	11	Week 2
M21136	PP09	SeverityofBurning	4	12	Week 2
M21136	PP09	SeverityofBurning	4	13	Week 2
M21136	PP09	SeverityofBurning	5	7	Week 2
M21136	PP09	SeverityofBurning	7	8	Week 2
M21136	PP10	SeverityofStinging	2	10	Week 2
M21136	PP10	SeverityofStinging	3	11	Week 2
M21136	PP10	SeverityofStinging	4	7	Week 2
M21136	PP10	SeverityofStinging	4	9	Week 2
M21136	PP10	SeverityofStinging	4	12	Week 2
M21136	PP10	SeverityofStinging	4	13	Week 2
M21136	PP10	SeverityofStinging	6	8	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;

jimbarbour · Posted 07-30-2021 04:42 PM

OK, here's a first step. This is pretty basic; it's probably not 100% of what you want, but let's start here, and then go from there.

I've got all the TestCD's separated out by week, but I see you have signs and symptoms separated. I don't know how to distinguish those, and I don't see the weeks -- or is the list of which are symptoms and which are signs? If so, what do you want the final output to look like?

Symptom	PP01, PP04, PP09, PP10, PP11
Sign	PP02, PP03, PP05, PP06, PP07, PP08

SAS Code:

DATA TEST ;
INFILE DATALINES DLM="09"X DSD;
input ID $	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	PP01	SeverityofItch	3	11	Week 2
M21136	PP01	SeverityofItch	4	7	Week 2
M21136	PP01	SeverityofItch	4	9	Week 2
M21136	PP01	SeverityofItch	4	12	Week 2
M21136	PP01	SeverityofItch	4	13	Week 2
M21136	PP01	SeverityofItch	5	10	Week 2
M21136	PP01	SeverityofItch	7	8	Week 2
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	PP04	SeverityofSkin Tightness	4	9	Week 2
M21136	PP04	SeverityofSkin Tightness	4	11	Week 2
M21136	PP04	SeverityofSkin Tightness	4	12	Week 2
M21136	PP04	SeverityofSkin Tightness	4	13	Week 2
M21136	PP04	SeverityofSkin Tightness	5	7	Week 2
M21136	PP04	SeverityofSkin Tightness	5	10	Week 2
M21136	PP04	SeverityofSkin Tightness	6	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	PP09	SeverityofBurning	1	10	Week 2
M21136	PP09	SeverityofBurning	4	9	Week 2
M21136	PP09	SeverityofBurning	4	11	Week 2
M21136	PP09	SeverityofBurning	4	12	Week 2
M21136	PP09	SeverityofBurning	4	13	Week 2
M21136	PP09	SeverityofBurning	5	7	Week 2
M21136	PP09	SeverityofBurning	7	8	Week 2
M21136	PP10	SeverityofStinging	2	10	Week 2
M21136	PP10	SeverityofStinging	3	11	Week 2
M21136	PP10	SeverityofStinging	4	7	Week 2
M21136	PP10	SeverityofStinging	4	9	Week 2
M21136	PP10	SeverityofStinging	4	12	Week 2
M21136	PP10	SeverityofStinging	4	13	Week 2
M21136	PP10	SeverityofStinging	6	8	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;
	SELECT	DISTINCT	
			Period
			,TestCD
		From	WORK.Test
		GROUP	BY	Period
		;
QUIT;

Which yields:

vnreddy · Posted 07-30-2021 05:02 PM

Hi @jimbarbour

Yes you are right symptoms and signs are grouped based on the TESTCD

We need to do the average of symptoms if we have atleast 2 questions answered out of 5 (here TEST is the question and SCORE is the answer)

We need to do the average of signs if we have atleast 3 questions answered out of 6 (here TEST is the question and SCORE is the answer)

I am looking for a final output like below: i have the arm details in a separate dataset, my plan is to once i figure out the symptoms and signs averages

then i can merge arm data and create a proc report as shown below.

/*after creating the the average from symptoms, signs and total score, i can use below logic to get the status */
/*CHG is the derived variable - averages of symptoms, signs and total score*/
/*PAR is defined based the CHG value*/

if CHG ne .  then do ; 
 if CHG>=15 then do;  PAR='Declined'; PARN=3; end; 
 else if CHG<=-15 then do; PAR='Improved';PARN=1; end; 
else  do; PAR='Stable'  ;PARN=2;end;
end; 
	   
if PAR= '' then do; 
PAR='Missing';
PARN=4;

Final output:

Period	Status	Statistic	Pmi xx mg N=xxx	Plast xx mg N=xxx	Placebo N=xxx
Week 1	Improved	n(%)	xx(x.x)	xx(x.x)	x(xx.x)
	Stable	n(%)	xx(x.x)	xx(x.x)	x(xx.x)
	Declined	n(%)	x(x.x)		x(x.x)
	Missing	n(%)	xx	x	X

Week 2	Improved	n(%)	x(x.x)	xx(x.x)	x(x.x)
	Stable	n(%)	xx(x.x)	xx(x.x)	x(x.x)
	Declined	n(%)	x(x.x)		x(x.x)
	Missing	n(%)	xx	Xx	x

jimbarbour · Posted 07-30-2021 05:25 PM

OK, I'm a bit unclear here. I don't have data like Placebo etc. I don't see any missing values. Are missing values to be imputed or ?

With the data that I have, what needs to be done? What can I help you with, with this data?

Jim

vnreddy · Posted 07-30-2021 05:34 PM

Hi @jimbarbour

Just to show case the final output i have given the screen shot of placebo. I can do that piece by merging this data with the ARM

Yes, we need to impute the missing values, ID, to obtain a symptom score on a given day, responses to at least 2 of the 5 questions must be available. If more than 3 questions are missing, the symptom score is considered missing. same applies to signs as well.

TEST = questions

Score = answer

Day = days in a week(for e.g., in Day 1 if ID answers only 1 question out of 5 questions then the symptoms average for that day will be missing)

Symptoms, signs, total score are derived from score 0 to 10 answered by ID

jimbarbour · Posted 07-30-2021 05:37 PM

OK, so exactly what do you need in the data that you have given me? Can you give me a mock up of what the SAS dataset would look like? Just base this on what data I have or what can be inferred from it.

Jim

vnreddy · Posted 07-30-2021 05:52 PM

I need some help in deriving the average of symptoms and sign score based on the requirement.

If i have the average scores of this i can do the rest of it, i have already done this ADAM dataset which has a VISIT week wise data.

Sample data which i provided is the sdtm data from qs dataset which has daily dairy qsdy(day) in the data which i provided.

my challenge is how i can derive the symptoms, signs and total average based on the number of the questions answered in a day

data which i provided looks similar to sdtm qs data

jimbarbour · Posted 07-30-2021 06:18 PM

OK, good. What you're telling me is helpful. Can you give me a mock-up of what the data should look like? Do you know what I mean by "mock-up"? I want to see a layout of what you want the SAS dataset to look like. You can do it in Excel and screen print it and then paste it int. Some description after you give me a picture would be helpful. I understand that we need averages, but it sounds like I need to determine missing values first.

Can I determine the missing values with the sample data I have?

Jim

vnreddy · Posted 07-30-2021 06:44 PM

Hi @jimbarbour

Thanks for the helping me out.

I am expecting output dataset something like below:

PAR - can be derived from TESTCD/TEST variables.

Period - available in data week 1, week 2

CHG - averages of symptoms, signs & total (e.g., if subjects answer less than 2 symptom questions avg. will be blank, just for sample i have created blank)

Ready to join fellow brilliant minds for the SAS Hackathon?

Classroom Training Available!