How do you add spec limits, cpk to PROC SHEWHART with a by variable?

[narnia649]

I have a data table that has the by variables (group_one & group_two) along with the upper and lower specifications (_lsl_ & _usl_). Looking at the SAS documentations, I see you can add a constant value to the lsl and usl options; however, I cannot get it to work using the data table columns _lsl_ and _usl_.

Is there a way to create the inset data table with the _lsl_ & _usl_ columns within the dataset?

Code Attempt:

DATA test;
INPUT id group_one $ group_two $ results _lsl_ _usl_;
DATALINES;
1 A A 1 2 4
2 A A 2 2 4
3 A A 3 2 4
4 A A 4 2 4
5 A A 5 2 4
6 B B 6 5 7
7 B B 7 5 7
8 B B 8 5 7
9 B B 9 5 7
10 B B 10 5 7
;
RUN;

PROC SHEWHART DATA=test;
	BY group_one group_two;
	IRCHART results*id / 
		lsl=_lsl_
		usl=_usl_;
	inset data = test lsl usl cp(6.4)/
		position=rm
		cshadow=black
		header="Summary Stats";
RUN;

Error:

1579  *DM Submits a SAS statement to the Program Editor, Log, or Procedure Output;
1580  dm log "OUT; CLEAR; LOG; CLEAR;" log continue;
1581  dm log 'next results; clear; cancel;' whostedit continue;
1582
1583  *Creates a new html result;
1584  ods html newfile=none;
NOTE: Writing HTML Body file: sashtml8.htm
1585
1586  DATA test;
1587  INPUT id group_one $ group_two $ results _lsl_ _usl_;
1588  DATALINES;

NOTE: The data set WORK.TEST has 10 observations and 6 variables.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds


1599  ;
1600  RUN;
1601
1602  PROC SHEWHART DATA=test;
1603      BY group_one group_two;
1604      IRCHART results*id /
1605          lsl=_lsl_
                  -----
                  22
                  76
ERROR 22-322: Syntax error, expecting one of the following: a numeric constant, a datetime constant, a missing value, (.
ERROR 76-322: Syntax error, statement will be ignored.
1606          usl=_usl_;
1607      inset data = test lsl usl cp(6.4)/
1608          position=rm
1609          cshadow=black
1610          header="Summary Stats";
1611  RUN;

NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SHEWHART used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 second

[PaigeMiller · Accepted solution]

1602  PROC SHEWHART DATA=test;
1603      BY group_one group_two;
1604      IRCHART results*id /
1605          lsl=_lsl_
                  -----
                  22
                  76
ERROR 22-322: Syntax error, expecting one of the following: a numeric constant, a datetime constant, a missing value, (.
ERROR 76-322: Syntax error, statement will be ignored.

 You need to provide a number after LSL=. You can't use _LSL_, that's not a number

[narnia649]

Thanks, yeah, I was able to gather that from the documentation.

The problem is the LSL & USL changes with each group variable.
Putting in a single number wouldn't reflect the specs changing between groups.
Is there another method to do this then?

[sbxkoenk]

Hello,

If you have SAS VIYA, you can use 

PROC SPC.

Use the SPECS= option on the IRCHART Statement (behind the forward slash):

SPECS=

Specifies an input data table that contains process specification limits and computes process capability indices

Cheers,

Koen

[PaigeMiller]

If you are interested in computing Cpk across BY groups, PROC CAPABILITY will do that.

If you want Cpk across BY groups and these are shown on a trend (SHEWHART) chart, I'm not aware of a way to do this. CORRECTION: you could certainly write a macro to do this.

[narnia649]

Thanks for the help. I ended up doing just as you mentioned and wrote a macro for it.

[Quentin]

I looked, thinking this was an obvious feature, but I don't see it in the documentation either.  Since PROC SHEWHART has a limits= input dataset, I thought it might be there, but that is only for input of process limits, not spec limits.  As @sbxkoenk showed there is a way do this in Viya with PROC SPC, maybe you could put in a SAS ballot suggestion (https://communities.sas.com/t5/SASware-Ballot-Ideas/idb-p/sas_ideas) that they add a SPECS= input dataset for PROC SHEWHART.

[narnia649]

Per @PaigeMiller , I wrote a macro to do this:

/* Place graphs in work directory so deleted. */
ods listing gpath="%sysfunc(getoption(work))";

*Closes html results;
ods html close;

*DM Submits a SAS statement to the Program Editor, Log, or Procedure Output;
dm log "OUT; CLEAR; LOG; CLEAR;" log continue;
dm log 'next results; clear; cancel;' whostedit continue;

*Creates a new html result;
ods html newfile=none;

DATA test;
INPUT id group_one $ group_two $ results _lsl_ _usl_;
DATALINES;
1 A A 1 2 4
2 A A 2 2 4
3 A A 3 2 4
4 A A 4 2 4
5 A A 5 2 4
6 B B 6 5 7
7 B B 7 5 7
8 B B 8 5 7
9 B B 9 5 7
10 B B 10 5 7
;
RUN;

DATA test;
	SET test;
	concat_columns = compress(cat(group_one, "_", group_two));
RUN;

PROC SQL NOPRINT;
	SELECT DISTINCT quote(trim(concat_columns))
	INTO :concat_filter SEPARATED BY "~"
	FROM test
	;
QUIT;


%MACRO loop_charts();
	%LET filter_length = %sysfunc(countw(&concat_filter,%str(~),mq));;
	%DO i=1 %TO &filter_length;

		%LET cur_cond = %scan(&concat_filter, &i, "~");	

		/* Create subset... */
		PROC SQL;
			CREATE TABLE test_subset AS
			SELECT *
			FROM test
			WHERE concat_columns IN ("&cur_cond")
			;
		QUIT;

		/* Grab USL & LSL */
		DATA null;
			SET test_subset END=LAST;

			IF LAST THEN DO;
				call symput("lsl_mac", _lsl_);
				call symput("usl_mac", _usl_);
			END;
		RUN;

		%PUT --> Lower Spec Limit &lsl_mac;

		PROC SHEWHART DATA=test_subset;
			IRCHART results*id / 
				lsl=&lsl_mac
				usl=&usl_mac;
			inset lsl="Lower Spec" usl="Upper Spec" cp(6.4)/
				position=rm
				cshadow=black
				header="Summary Stats";
		RUN;

	%END;
%MEND loop_charts;

%loop_charts();