Obsidian | Level 7

## How do I create an xmr control chart in VA 7.4

I'm not having much luck trying to work out how to take a data series and create a XmR control chart in VA. I can easily do this in excel. Here's a typical data set.

If you treat the columns as A - E in an excel spreadsheet.

Number clients is the straight data which updates each month.
Average is the average of the column of clients over the period of the data set.  =AVERAGE(B:B) in excel.
Range is the difference between one month and the previous =ABS(B6-B7) in excel

Range average is well the range average =AVERAGE(F:F) in excel

LCL is the lower limit calculation =C6-(2.66*G6) in excel

UCL is the upper limit calculation  =C6+(2.66*G6) in excel

While I may have to change the upper and lower limits if on occasion trends continue for most of the subsets that fall off this data they do stay within the upper and lower limits most of the time. As the client number data drops down from a national, to a regional and then a local  level obviously it is preferable that the calculations are dynamic. I could get away with fixed lines for the average and lower limits if that was the only way to do this - I'd just have to do multiple graphs. The move from excel and manual updating to VA and auto updating the client numbers outweighs the intial effort to do this.

I only have access to VA and not to building data tables that could do these calculations at the back end.

 Month Number clients Average LCL UCL Range Range average Jul-16 22,433 22,264 21,806 22,722 172 Aug-16 22,612 22,264 21,806 22,722 179 172 Sep-16 22,660 22,264 21,806 22,722 48 172 Oct-16 22,380 22,264 21,806 22,722 280 172 Nov-16 22,545 22,264 21,806 22,722 165 172 Dec-16 22,903 22,264 21,806 22,722 358 172 Jan-17 22,843 22,264 21,806 22,722 60 172 Feb-17 22,595 22,264 21,806 22,722 248 172 Mar-17 22,078 22,264 21,806 22,722 517 172 Apr-17 21,942 22,264 21,806 22,722 136 172 May-17 21,855 22,264 21,806 22,722 87 172 Jun-17 21,720 22,264 21,806 22,722 135 172 Jul-17 21,578 22,264 21,806 22,722 142 172 Aug-17 21,551 22,264 21,806 22,722 27 172 Sep-17 21,501 22,264 21,806 22,722 50 172 Oct-17 21,451 22,264 21,806 22,722 50 172 Nov-17 21,646 22,264 21,806 22,722 195 172 Dec-17 22,352 22,264 21,806 22,722 706 172 Jan-18 22,460 22,264 21,806 22,722 108 172 Feb-18 22,355 22,264 21,806 22,722 105 172 Mar-18 22,102 22,264 21,806 22,722 253 172 Apr-18 22,133 22,264 21,806 22,722 31 172 May-18 22,028 22,264 21,806 22,722 105 172 Jun-18 22,109 22,264 21,806 22,722 81 172 Jul-18 22,024 22,264 21,806 22,722 85 172 Aug-18 22,188 22,264 21,806 22,722 164 172 Sep-18 22,230 22,264 21,806 22,722 42 172 Oct-18 22,034 22,264 21,806 22,722 196 172 Nov-18 22,059 22,264 21,806 22,722 25 172 Dec-18 22,776 22,264 21,806 22,722 717 172 Jan-19 22,892 22,264 21,806 22,722 116 172 Feb-19 22,703 22,264 21,806 22,722 189 172 Mar-19 22,472 22,264 21,806 22,722 231 172 Apr-19 22,265 22,264 21,806 22,722 207 172 May-19 22,345 22,264 21,806 22,722 80 172 Jun-19 22,454 22,264 21,806 22,722 109 172 Jul-19 22,334 22,264 21,806 22,722 120 172 Aug-19 22,575 22,264 21,806 22,722 241 172 Sep-19 22,682 22,264 21,806 22,722 107 172 Oct-19 22,696 22,264 21,806 22,722 14 172

Any help or thoughts would be appreciated.

It would be quite cool down the track to have a specific graph option for these type of control charts with the option to modify the 2.66 calculation in the limit workings  =C6-(2.66*G6). The other calculations are quite standard.

8 REPLIES 8
Quartz | Level 8

## Re: How do I create an xmr control chart in VA 7.4

In SAS VA 7.4 I don't know but in SAS Viya proc scp is now available in CAS, creating the tables for XMR charts. See

Process Monitoring in SAS Viya with the SPC Procedure https://www.youtube.com/watch?v=HNmJicNznIg

A visual component you have to build yourself though.It would be great to see a visual object in SAS VA someday to create XMR charts OOTB.

Obsidian | Level 7

## Re: How do I create an xmr control chart in VA 7.4

Unfortunately we do not have Viya at work. Nice to know it has been thought about though.

Trying to move people's thinking away from targets and the often negative behaviour and practice  that drives. Being able to show an alternate way of thinking via reporting is a crucial part of that.

Quartz | Level 8

## Re: How do I create an xmr control chart in VA 7.4

Hear hear

Obsidian | Level 7

## Re: How do I create an xmr control chart in VA 7.4

Been trying to do that for 20 years unfortunately. The recent move to VA for reporting in our organisation has democratised the use of data. The ability to build reports locally that can be utilised and viewed nationally is driving some good change in how reporting is viewed and used and influences.

Having views that don't come from the centre is making people think.

Obsidian | Level 7

## Re: How do I create an xmr control chart in VA 7.4

Just wondering if anyone can come up for a solution to this. It seems weird to me that something I can easily do in excel I can't figure out here. I was thinking it was me not quite getting how to fully utilise VA but maybe not.

PROC Star

## Re: How do I create an xmr control chart in VA 7.4

@Noel88  - You could raise this as a SASware Ballot suggestion: https://communities.sas.com/t5/SASware-Ballot-Ideas/idb-p/sas_ideas

Obsidian | Level 7

## Re: How do I create an xmr control chart in VA 7.4

Cool. Thanks for letting me know.

Obsidian | Level 7

## Re: How do I create an xmr control chart in VA 7.4

Managed to work out a work around where I can calculate the mean and limits in excel and then add these to the graph as reference lines. Means while the data updates each month automatically I need to manually make any adjustments to the mean, UCL and LCL.

Works as an interim solution till I find something better and gives the report visibility needed throughout the month

Discussion stats
• 8 replies
• 3992 views
• 1 like
• 3 in conversation