topic Count positive and negative and calculate in SAS Programming

Count positive and negative and calculate

Moraes86 — Tue, 11 Jan 2022 23:10:05 GMT

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

Result

days

Tested

date

Result

days

Tested

Count positive

count negative

Color

20-Dec

positive

Yes

20-Dec

positive

Yes

8 from 10

blue

21-Dec

positive

Yes

21-Dec

positive

Yes

8 from 10

blue

22-Dec

positive

Yes

22-Dec

positive

Yes

8 from 10

blue

23-Dec

positive

Yes

23-Dec

positive

Yes

8 from 10

blue

24-Dec

negative

Yes

24-Dec

negative

Yes

8 from 10

blue

25-Dec

positive

Yes

25-Dec

positive

Yes

8 from 10

blue

26-Dec

positive

Yes

26-Dec

positive

Yes

8 from 10

blue

27-Dec

positive

Yes

27-Dec

positive

Yes

8 from 10

blue

28-Dec

negative

Yes

28-Dec

negative

Yes

8 from 10

blue

29-Dec

positive

Yes

29-Dec

positive

Yes

8 from 10

blue

30-Dec

negative

Yes

30-Dec

negative

Yes

3 from 8

red

31-Dec

negative

Yes

31-Dec

negative

Yes

3 from 8

red

1-Jan

negative

Yes

1-Jan

negative

Yes

3 from 8

red

2-Jan

positive

Yes

2-Jan

positive

Yes

3 from 8

red

3-Jan

positive

Yes

3-Jan

positive

Yes

3 from 8

red

4-Jan

positive

Yes

4-Jan

positive

Yes

3 from 8

red

5-Jan

positive

Yes

5-Jan

positive

Yes

3 from 8

red

6-Jan

negative

Yes

6-Jan

positive

Yes

3 from 8

red

7-Jan

negative

Yes

7-Jan

negative

Yes

3 from 3

green

8-Jan

negative

Yes

8-Jan

negative

Yes

3 from 3

green

9-Jan

negative

Yes

9-Jan

negative

Yes

3 from 3

green

Thanks all,

Daniel

Re: Count positive and negative and calculate

acordes — Wed, 12 Jan 2022 00:29:24 GMT

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;

Re: Count positive and negative and calculate

andreas_lds — Wed, 12 Jan 2022 06:52:10 GMT

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"

Re: Count positive and negative and calculate

Moraes86 — Wed, 12 Jan 2022 18:10:25 GMT

Thanks andreas_lds! It helped me a lot

Re: Count positive and negative and calculate

Moraes86 — Wed, 12 Jan 2022 18:11:00 GMT

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