Aggregating and Plotting Quality Control Data

[BlueNose]

Dear SAS Users,

I have a complex question, it involves mainly programming, but also a little bit of graphics and methodology, so I chose this board, I hope it's okay !

I have quality control data coming from a lab (photo attached).

The lab has two rooms. Each room has several areas. Each area has several locations within this area. For each location within area within room, two different bacteria tests are being made. Each has a maximum allowed limit. The test was performed 3 times a day for 16 days in a row. Each measure is the counts of bacteria found.

I have this data organized in a SAS dataset, a "long file", the way SAS "likes it" 🙂

I wish to do the following tasks, and I am not sure how to:

1) Aggregate - calculate the average count of bacteria per day, so I end up with a daily reading rather than 3 of them.

2) Graph the averages - 16 points. The x-axis is the date, the y-axis is the average. In addition, I wish to add a horizontal line with a value coming from the "Limit" column. This task should be done BY room / area / location / type (limit will vary by this combination). The data is already sorted for the by statement to work. Dots should be connected by a line.

3) In addition to the stated graph above, I thought of using a control chart, so I can see / test for trends in the data. However, non of the control charts I know seems to fit (Xbar, C, U, P, NP). The only one that might work is the Xbar, but I am not sure it's correct to use it. Which control chart shall I use, and is it possible to plot, for example, the Xbar chart (which aggregates the daily data I think), and to add another horizontal reference line to the plot, again, doing it by all the variables ?

I have a feeling that these tasks are fairly easy, if you know how to....

I am using SAS 9.4.

Thank you in advance for any tips and assistance you can provide. Without using SAS and the by statement I will have quite a lot of work doing this manually....

 

[Reeza]

1. PROC MEANS https://github.com/statgeek/SAS-Tutorials/blob/master/proc_means_basic.sas

2. SGPLOT - SERIES statement, find the chart closest to what you want and see the code: http://support.sas.com/sassamples/graphgallery/PROC_SGPLOT.html

3. Not sure what that graph would be. SAS has a Quality Control module, but it's not included in the Base license so you'd have to verify you have a license. 

proc product_status;run; *tells you what is licensed;