How do I create an xmr control chart in VA 7.4

[Noel88]

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.

[Frank_Boekamp]

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.

[Noel88]

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.

[Frank_Boekamp]

Hear hear

[Noel88]

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.

[Noel88]

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.

[SASKiwi]

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

Any improvements would only be added to the latest version of VA though.

[Noel88]

Cool. Thanks for letting me know.

[Noel88]

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