Count positive and negative and calculate

[Moraes86]

Hi all,

I am showing below the data that I have and what I want. 

I wrote in the column count positive "8 from 10", and the column count negative "3 from 8"just as an example.

I need: - to count negative and positive. 

column color: all result negative: green

                      ≥ result 75% negative: blue

                      < result 75% negative: red

The conditions are: column "days" should be less than 90 days,  and column "Tested"  should be "Yes". 

Have

Want

date

ID

Result

days

Tested

date

ID

Result

days

Tested

Count positive

count negative

Color

20-Dec

A

positive

21

Yes

20-Dec

A

positive

21

Yes

8 from 10

blue

21-Dec

A

positive

20

Yes

21-Dec

A

positive

20

Yes

8 from 10

blue

22-Dec

A

positive

19

Yes

22-Dec

A

positive

19

Yes

8 from 10

blue

23-Dec

A

positive

18

Yes

23-Dec

A

positive

18

Yes

8 from 10

blue

24-Dec

A

negative

17

Yes

24-Dec

A

negative

17

Yes

8 from 10

blue

25-Dec

A

positive

16

Yes

25-Dec

A

positive

16

Yes

8 from 10

blue

26-Dec

A

positive

15

Yes

26-Dec

A

positive

15

Yes

8 from 10

blue

27-Dec

A

positive

14

Yes

27-Dec

A

positive

14

Yes

8 from 10

blue

28-Dec

A

negative

13

Yes

28-Dec

A

negative

13

Yes

8 from 10

blue

29-Dec

A

positive

12

Yes

29-Dec

A

positive

12

Yes

8 from 10

blue

30-Dec

B

negative

11

Yes

30-Dec

B

negative

11

Yes

3 from 8

red

31-Dec

B

negative

10

Yes

31-Dec

B

negative

10

Yes

3 from 8

red

1-Jan

B

negative

9

Yes

1-Jan

B

negative

9

Yes

3 from 8

red

2-Jan

B

positive

8

Yes

2-Jan

B

positive

8

Yes

3 from 8

red

3-Jan

B

positive

7

Yes

3-Jan

B

positive

7

Yes

3 from 8

red

4-Jan

B

positive

6

Yes

4-Jan

B

positive

6

Yes

3 from 8

red

5-Jan

B

positive

5

Yes

5-Jan

B

positive

5

Yes

3 from 8

red

6-Jan

B

negative

4

Yes

6-Jan

B

positive

4

Yes

3 from 8

red

7-Jan

C

negative

3

Yes

7-Jan

C

negative

3

Yes

3 from 3

green

8-Jan

C

negative

2

Yes

8-Jan

C

negative

2

Yes

3 from 3

green

9-Jan

C

negative

1

Yes

9-Jan

C

negative

1

Yes

3 from 3

green

Thanks all,

Daniel

[acordes · Accepted solution]

data have;
infile datalines DSD DLM='09'x;
input date$ ID$ Result$ days Tested$;
datalines;
20-Dec	A	positive	21	Yes 
21-Dec	A	positive	20	Yes 
22-Dec	A	positive	19	Yes 
23-Dec	A	positive	18	Yes 
24-Dec	A	negative	17	Yes 
25-Dec	A	positive	16	Yes 
26-Dec	A	positive	15	Yes 
27-Dec	A	positive	14	Yes 
28-Dec	A	negative	13	Yes 
29-Dec	A	positive	12	Yes 
30-Dec	B	negative	11	Yes 
31-Dec	B	negative	10	Yes 
1-Jan	B	negative	9	Yes 
2-Jan	B	positive	8	Yes 
3-Jan	B	positive	7	Yes 
4-Jan	B	positive	6	Yes 
5-Jan	B	positive	5	Yes 
6-Jan	B	negative	4	Yes 
7-Jan	C	negative	3	Yes 
8-Jan	C	negative	2	Yes 
9-Jan	C	negative	1	Yes 
;
run;

proc format;
value color_code
low-0.75="red"
0.75-<1="blue"
1="green"
;
run;


proc sql;
create table have2 as 
select a.*, b.oks, b.oks/a.tot as ratio format=percent9.2, b.oks/a.tot as color_code format=color_code. 
from 
(select *, count(*) as tot 
from have group by id) a left join (select distinct id, count(*) as oks 
from have where days le 90 and result^="negative" group by id) b 
on a.id=b.id;
quit;

[Moraes86]

Thank you acordes for your coding....thanks all

[andreas_lds]

Please post data in usable form! Those tables are unusable forcing those willing to help to spend additional time.

Your description:

column color: all result negative: green

≥ result 75% negative: blue

< result 75% negative: red

ID = A has 20% negative, but you expect blue as colour.

General course of action:

- start with proc freq to count, it gives you percentages for free

- use a data step to merge the results from proc freq back into the dataset

- in that step using a self-defined format to translate values into text (as shown by @acordes)

- if you want a report with coloured cells, have a look at papers dealing with "traffic lightning"

[Moraes86]

Thanks andreas_lds! It helped me a lot