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
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;
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;
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"
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.