Flag missing days by days and week

[vnreddy]

Hi,

Could someone help me with below problem, i have subject data with days(qsdy), arm, rsn(score) and visit(week1, week2,...till week 52).

I want to create an intermediate table : if subjects missing to give rsn for more than 3 days in a week then the score should be blank, else rsn = score

similarly i want the average of the score by weekly based on the intermediate table which we derived.

Input data:

data test;
infile datalines dlm='09'X dsd missover;
input ID$ ARM $ RSN QSDY VISIT$;
datalines;
M21136 M 3 mg 4 1 Week 1
M21136 M 3 mg 2 2 Week 1
M21136 M 3 mg 1 3 Week 1
M21136 M 3 mg 4 4 Week 1
M21136 M 3 mg 1 5 Week 1
M21136 M 3 mg 2 6 Week 1
M21137 P 3 8 Week 2
M21137 P 4 9 Week 2
M21138 M 3 mg 3 15 Week 3
M21138 M 3 mg 2 16 Week 3
M21138 M 3 mg 1 17 Week 3
M21138 M 3 mg 4 18 Week 3
M21138 M 3 mg 2 19 Week 3
M21138 M 3 mg 1 20 Week 3
M21139 L 3 mg 2 21 Week 4
M21139 L 3 mg 1 22 Week 4
;
run;

Expected output 1:

ID	ARM	RSN	QSDY	VISIT	score
M21136	M 3 mg	4	1	Week 1	4
M21136	M 3 mg	2	2	Week 1	2
M21136	M 3 mg	1	3	Week 1	1
M21136	M 3 mg	4	4	Week 1	4
M21136	M 3 mg	1	5	Week 1	1
M21136	M 3 mg	2	6	Week 1	2
M21137	P	3	8	Week 2
M21137	P	4	9	Week 2
M21138	M 3 mg	3	15	Week 3	3
M21138	M 3 mg	2	16	Week 3	2
M21138	M 3 mg	1	17	Week 3	1
M21138	M 3 mg	4	18	Week 3	4
M21138	M 3 mg	2	19	Week 3	2
M21138	M 3 mg	1	20	Week 3	1
M21139	L 3 mg	2	21	Week 4
M21139	L 3 mg	1	22	Week 4

expected output 2:

ID	ARM	VISIT	Average
M21136	M 3 mg	Week 1	23.33333
M21137	P	Week 2
M21138	M 3 mg	Week 3	21.66667
M21139	L 3 mg	Week 4

[PaigeMiller]

Could you please modify your SAS code so it reads the data properly? Thanks!

[vnreddy]

data test;
infile datalines delimiter=' ';
input ID$1-6 ARM$8-14 RSN 17 QSDY VISIT$23-30;
datalines;
M21136	M 3 mg	4	1	Week 1
M21136	M 3 mg	2	2	Week 1
M21136	M 3 mg	1	3	Week 1
M21136	M 3 mg	4	4	Week 1
M21136	M 3 mg	1	5	Week 1
M21136	M 3 mg	2	6	Week 1
M21137	P		3	8	Week 2
M21137	P		4	9	Week 2
M21138	M 3 mg	3	15	Week 3
M21138	M 3 mg	2	16	Week 3
M21138	M 3 mg	1	17	Week 3
M21138	M 3 mg	4	18	Week 3
M21138	M 3 mg	2	19	Week 3
M21138	M 3 mg	1	20	Week 3
M21139	L 3 mg	2	21	Week 4
M21139	L 3 mg	1	22	Week 4
;
run;

sorry, code is now fixed for Base SAS.

[PaigeMiller]

proc sql;
    create table test1 as select *,count(qsdy) as days,
        case when calculated days>3 then rsn else . end as score
        from test group by id,visit;
quit;

I have no idea how you get an average of 23 from this data.

[vnreddy]

Hi @PaigeMiller 

it's average of that particular id : avg(score)*10

[andreas_lds]

Please re-check the code you have posted, RSN is missing for all observations.

EDIT: Also, please explain what happened to the variable "score" shown in the table, but not in the data step you have posted. Further explanation is required, so that we have a chance to understand the logic you want applied. 

[vnreddy]

RSN = score

score = average

[PaigeMiller]

it's average of that particular id : avg(score)*10

To get average score by ID, use PROC SUMMARY, then multiply the results by 10.