topic Re: Proc TEMPLATE crashes Proc FREQ? in ODS and Base Reporting

Proc TEMPLATE crashes Proc FREQ?

jimbarbour — Fri, 27 Oct 2017 18:26:22 GMT

I'm encountering an odd situation. I have a Proc FREQ that I've been running for some time; pretty standard stuff. If however, I run a Proc TEMPLATE with some specific parameters, Proc FREQ no longer runs. The offending code appears to be:

				/	_row_label_	^=	' '
				/	_col_label_	^=	' '

which is in a DEFINE HEADER statement as follows:

	DEFINE	HEADER	tableof;
		TEXT	"Table of "	_row_label_	" by "	_col_label_
				/	_row_label_	^=	' '
				/	_col_label_	^=	' '
					;
	END;

Full code is below.

The message I get is:

ERROR: WHERE clause operator requires numeric variables.

The message is fairly nonsensical in that the variable is character, and the FREQ runs fine if one or the other of the _row_label_ or _column_label_ statements is commented out.

I receive no notes, warnings, or errors when I run Proc TEMPLATE. Run to run, I do not change my data or the code in my Proc FREQ. The only thing I am changing is the code in the template.

Has anyone encountered something like this here? It's not a show stopper. The worst that can happen is that I might print a blank row or column label if a label were blank. It just strikes me as really really odd that the parameters in my Proc TEMPLATE are causing a working Proc FREQ to fail.

Any help much appreciated,

Jim

OPTION	CENTER;
ODS	NOPROCTITLE

PROC	TEMPLATE;
	DEFINE	CROSSTABS	Base.Freq.CrossTabFreqs;

	DEFINE	HEADER	tableof;
		TEXT	"Table of "	_row_label_	" by "	_col_label_
				/	_row_label_	^=	' '
				/	_col_label_	^=	' '
					;
	END;

	DEFINE	HEADER	rowsheader;
		TEXT	_row_label_	/	_row_label_	^=	' ';
		TEXT	_row_name_;
	END; 

	DEFINE	HEADER	colsheader;
		TEXT	_col_label_	/	_col_label_	^=	' ';
		TEXT	_col_name_;
	END;

	cols_header=colsheader;
	rows_header=rowsheader;
	HEADER	tableof;

	END;
RUN;

PROC	FREQ	DATA=WORK.Bankruptcies		(OBS=&Obs);
		WHERE	tr_acctcond	IN	('67', '69');
	TABLES		tr_y2k_bk_year	*	tr_acctcond
				/	NOROW	NOCOL
				;
RUN;

Re: Proc TEMPLATE crashes Proc FREQ?

ballardw — Fri, 27 Oct 2017 18:56:32 GMT

Your template apparently doesn't have any cellvalue entry to place results in. My base crosstabs shows:

      cellvalue Frequency Expected Deviation CellChiSquare TotalPercent       
         Percent RowPercent ColPercent CumColPercent;

for the values in the body of the table. You may want to review more of the base table syntax.

I am also not sure what you are attempting with:

	HEADER	tableof;

after the lines;

cols_header=colsheader;

rows_header=rowsheader;

With your template I just get

The FREQ Procedure
and no table.
 
Which version of SAS are you running?

Re: Proc TEMPLATE crashes Proc FREQ?

jimbarbour — Fri, 27 Oct 2017 20:33:04 GMT

I'm running SAS 9.4

    SAS (r) Proprietary Software Release 9.4  TS1M4

My thought was that only those things that I was specifying would be overridden. I don't see any irregularities in my output so long as I comment out one or other of the two parameters cited in my original post. See full output, below (last table is the output of the Proc FREQ).

The "tableof" definition controls this line: "Frequency of Bankruptcy Year by Account Condition" (see output, below). It replaces the variable names with labels.

An ODS trace reveals the following:

Output Added:
-------------
Name:       CrossTabFreqs
Label:      Cross-Tabular Freq Table
Template:   Base.Freq.CrossTabFreqs
Path:       Freq.Table1.CrossTabFreqs
-------------

The SAS data set associated with CrossTabFreqs, when saved has the below variables, but I don't think the contents of the SAS data set are at issue here. Things like _row_label_ and _col_label_ are output attributes and are not vars in the SAS data set.

Alphabetic List of Variables and Attributes
#	Variable	Type	Len	Label
6	Frequency	Num	8
8	Missing	Num	8	Frequency Missing
7	Percent	Num	8
1	Table	Char	256
5	_TABLE_	Num	8	Table Number
4	_TYPE_	Char	2	Variables Contributing to Observation
3	tr_acctcond	Char	2	Account Condition
2	tr_y2k_bk_year	Char	8	Bankruptcy Year

2009

0.01

0.02

2010

3941

4.18

856

0.91

4797

5.09

2011

10427

11.06

2208

2.34

12635

13.41

2012

10458

11.10

2461

2.61

12919

13.71

2013

10385

11.02

2538

2.69

12923

13.71

2014

11257

11.95

2874

3.05

14131

15.00

2015

11146

11.83

3947

4.19

15093

16.02

2016

12097

12.84

5154

5.47

17251

18.31

2017

2751

2.92

1718

1.82

4469

4.74

Total

72467

76.90

21768

23.10

94235

100.00

The really wild thing is that even if I comment out the WHERE clause, I still get the WHERE clause error message.

Re: Proc TEMPLATE crashes Proc FREQ?

jimbarbour — Fri, 27 Oct 2017 20:27:02 GMT

A dump of the ODS table reveals no obvious irregularities. I'm truly stumped.

Obs	Table	tr_y2k_bk_year	tr_acctcond	_TYPE_	_TABLE_	Frequency	Percent	Missing
1	Table tr_y2k_bk_year * tr_acctcond	2009	67	11	1	5	0.005	.
2	Table tr_y2k_bk_year * tr_acctcond	2009	69	11	1	12	0.013	.
3	Table tr_y2k_bk_year * tr_acctcond	2009		10	1	17	0.018	.
4	Table tr_y2k_bk_year * tr_acctcond	2010	67	11	1	3941	4.182	.
5	Table tr_y2k_bk_year * tr_acctcond	2010	69	11	1	856	0.908	.
6	Table tr_y2k_bk_year * tr_acctcond	2010		10	1	4797	5.090	.
7	Table tr_y2k_bk_year * tr_acctcond	2011	67	11	1	10427	11.065	.
8	Table tr_y2k_bk_year * tr_acctcond	2011	69	11	1	2208	2.343	.
9	Table tr_y2k_bk_year * tr_acctcond	2011		10	1	12635	13.408	.
10	Table tr_y2k_bk_year * tr_acctcond	2012	67	11	1	10458	11.098	.
11	Table tr_y2k_bk_year * tr_acctcond	2012	69	11	1	2461	2.612	.
12	Table tr_y2k_bk_year * tr_acctcond	2012		10	1	12919	13.709	.
13	Table tr_y2k_bk_year * tr_acctcond	2013	67	11	1	10385	11.020	.
14	Table tr_y2k_bk_year * tr_acctcond	2013	69	11	1	2538	2.693	.
15	Table tr_y2k_bk_year * tr_acctcond	2013		10	1	12923	13.714	.
16	Table tr_y2k_bk_year * tr_acctcond	2014	67	11	1	11257	11.946	.
17	Table tr_y2k_bk_year * tr_acctcond	2014	69	11	1	2874	3.050	.
18	Table tr_y2k_bk_year * tr_acctcond	2014		10	1	14131	14.995	.
19	Table tr_y2k_bk_year * tr_acctcond	2015	67	11	1	11146	11.828	.
20	Table tr_y2k_bk_year * tr_acctcond	2015	69	11	1	3947	4.188	.
21	Table tr_y2k_bk_year * tr_acctcond	2015		10	1	15093	16.016	.
22	Table tr_y2k_bk_year * tr_acctcond	2016	67	11	1	12097	12.837	.
23	Table tr_y2k_bk_year * tr_acctcond	2016	69	11	1	5154	5.469	.
24	Table tr_y2k_bk_year * tr_acctcond	2016		10	1	17251	18.306	.
25	Table tr_y2k_bk_year * tr_acctcond	2017	67	11	1	2751	2.919	.
26	Table tr_y2k_bk_year * tr_acctcond	2017	69	11	1	1718	1.823	.
27	Table tr_y2k_bk_year * tr_acctcond	2017		10	1	4469	4.742	.
28	Table tr_y2k_bk_year * tr_acctcond		67	01	1	72467	76.900	.
29	Table tr_y2k_bk_year * tr_acctcond		69	01	1	21768	23.100	.
30	Table tr_y2k_bk_year * tr_acctcond			00	1	94235	100.000	0

Re: Proc TEMPLATE crashes Proc FREQ?

ballardw — Fri, 27 Oct 2017 23:28:42 GMT

I am also running SAS 9.4 at the same service level.

It would be (slightly in this case) more helpful to use a SAS supplied data set for your examples. I tested your template with
Proc freq data=sashelp.class;

tables sex*age;

run;

And as previously mentioned, had no output except the Proc freq label.

I suggest looking into which template Path setting stores your templates an perhaps cleaning out things. You may be inheriting something from a another template that is causing problems.

Re: Proc TEMPLATE crashes Proc FREQ?

jimbarbour — Fri, 27 Oct 2017 23:41:30 GMT

I ran a FREQ with DATA=sashelp.class two times. The first run is with no labels. The second run is with labels applied to the two variables (sex and age).

Everything works as I would expect it to. The var names are replaced by the labels because of the template overrides I created previously. When no labels are present, then the var names are not replaced.

A problem only occurs when I try to check both variables to see if they are present as part of the template override, and it is a bizarre problem indeed, the template overrides causing Proc FREQ to fail.

5.26

10.53

0.00

47.37

5.26

15.79

5.26

10.53

5.26

52.63

Total

10.53

26.32

15.79

21.05

5.26

100.00

Run date = 2017-10-27. Run time = 16:10:54. Observations Processed = MAX.

5.26

10.53

0.00

47.37

5.26

15.79

5.26

10.53

5.26

52.63

Total

10.53

26.32

15.79

21.05

5.26

100.00

Run date = 2017-10-27. Run time = 16:10:54. Observations Processed = MAX.

Re: Proc TEMPLATE crashes Proc FREQ?

jimbarbour — Sat, 28 Oct 2017 00:13:58 GMT

For comparative purposes, I did a runs with no template overrides, both with and without labels, and then the same runs with my template overrides. It's instructive to see that the final HEADER command under the DEFINE CROSSTABS sets the header to what I built in my DEFINE HEADER. The results are consistent with the template overrides I coded.

Run without labels and without Template overrides

The FREQ Procedure

5.26

10.53

0.00

47.37

5.26

15.79

5.26

10.53

5.26

52.63

Total

10.53

26.32

15.79

21.05

5.26

100.00

Run date = 2017-10-27. Run time = 17:05:27. Observations Processed = MAX.

Run with labels and without Template overrides

The FREQ Procedure

5.26

10.53

0.00

47.37

5.26

15.79

5.26

10.53

5.26

52.63

Total

10.53

26.32

15.79

21.05

5.26

100.00

Run date = 2017-10-27. Run time = 17:05:27. Observations Processed = MAX.

Run without labels and with Template overrides

5.26

10.53

0.00

47.37

5.26

15.79

5.26

10.53

5.26

52.63

Total

10.53

26.32

15.79

21.05

5.26

100.00

Run date = 2017-10-27. Run time = 17:05:27. Observations Processed = MAX.

Run with labels and with Template overrides

5.26

10.53

0.00

47.37

5.26

15.79

5.26

10.53

5.26

52.63

Total

10.53

26.32

15.79

21.05

5.26

100.00

Run date = 2017-10-27. Run time = 17:05:27. Observations Processed = MAX.

Re: Proc TEMPLATE crashes Proc FREQ?

Cynthia_sas — Sat, 28 Oct 2017 00:41:10 GMT

Hi:

Your template change seems to be based on this Tech Support note: http://support.sas.com/kb/48/277.html. However, there are some notable differences between what you have and what is in the note. The major difference that I note is that you are NOT using EDIT, in all the time I've been working with PROC TEMPLATE and TABLE templates, the example shown in the Tech Support note, with EDIT instead of DEFINE is what I do with table templates used by SAS procedures. I only use DEFINE TABLE when I am actually defining my own table template.

Second, you do NOT show your ODS PATH, I assume you are doing a "prepend" as shown.

My suggestion is that you open a track with Tech Support on this.

cynthia

Re: Proc TEMPLATE crashes Proc FREQ?

jimbarbour — Sat, 28 Oct 2017 19:58:35 GMT

Hi, Cynthia,

I was kind of hoping you'd weigh in. 🙂 (you were super helpful when I was trying to colorize a Proc Report about a year ago)

No, I wasn't aware of that Tech note. I just Googled something about getting rid of vars and using labels for Proc FREQ and then tried the example I found. I'll change my code to match what the Tech note says and give it a re-try.

Jim

Re: Proc TEMPLATE crashes Proc FREQ?

jimbarbour — Sat, 28 Oct 2017 21:38:19 GMT

Hmm. Curiouser and curiouser. If I used "EDIT", the "Table of X by Y" header still shows the Vars, not the labels. However, if I use "DEFINE", then the "Table of X by Y" header works but I lose the little "Frequency" and "Percent" labels off to the side. This is with making no other code changes. See output from comparative runs, below. See full code, far below.

Current ODS PATH list is: 

1. WORK.TEMPLAT(UPDATE) 
2. SASUSER.TEMPLAT(READ) 
3. SASHELP.TMPLMST(READ)

Incidentally, I figured out how to get rid of the false error message about a WHERE clause (there is no WHERE clause in this code). Rather than coding the following (as I originally did):

		DEFINE	TableOf;
			TEXT	"Tables of "	_row_label_	" by "	_col_label_
					/	_row_label_	^=	' '		
					/	_col_label_	^=	' '
					;
		END;

Use instead an "OR":

		DEFINE	TableOf;
			TEXT	"Tables of "	_row_label_	" by "	_col_label_
					/	_row_label_	^=	' '		OR		_col_label_	^=	' '
					;
		END;

The result I've got is good enough although it's odd that the EDIT as described in the Tech note doesn't work.

Jim

Using: 'EDIT Base.Freq.CrossTabFreqs'

5.26

10.53

0.00

47.37

5.26

15.79

5.26

10.53

5.26

52.63

Total

10.53

26.32

15.79

21.05

5.26

100.00

Run date = 2017-10-28. Run time = 14:24:15. Observations Processed = MAX.

Using: 'DEFINE CROSSTABS Base.Freq.CrossTabFreqs'

5.26

10.53

0.00

47.37

5.26

15.79

5.26

10.53

5.26

52.63

Total

10.53

26.32

15.79

21.05

5.26

100.00

Run date = 2017-10-28. Run time = 14:24:15. Observations Processed = MAX.

Full code:

/*-------------------------------------------------------------------------------------------------*/
/*  Program:  Template_Override_Demo2
/*	Author:   Jim Barbour
/*	Date:     27 October 2017
/*  Run Time: Depends on the number of input records read.
/*	Remarks:  This is to demonstrate how to customize the output of PROC FREQ.
/*-------------------------------------------------------------------------------------------------*/
**	Environmental settings	**;

%LET		NoMacs		=	;			**	Set to asterisk to turn off control macros.  Set to NULL to allow control macros to run.	**;
%LET		SEG			=	;			**	Set to asterisk when using SAS EG to initialize macros.  Set to NULL otherwise.	**;

&SEG		%INCLUDE		'/ca_analytics/macros/Initialize.sas';

TITLE;
&NoMacs		%Time_Stamp_SEG;
&NoMacs		%Reset_ReturnCode;

*****************************;
OPTION		REPLACE;
OPTION		VARINITCHK=WARN;
OPTION		VARLENCHK=WARN;
OPTION		ERRORCHECK=STRICT;
OPTION		MSGLEVEL=I;
OPTION		THREADS;
OPTION		CPUCOUNT=4;
OPTION		NOSYMBOLGEN;
OPTION		CENTER;
*****************************;

*--------------------------------------------------------------------------------------------------*;

ODS			noPROCTITLE;
ODS			PATH	SHOW;
/*ODS		PATH	(PREPEND)	WORK.Templat(UPDATE);*/
/*ODS		PATH	SHOW;*/

*--------------------------------------------------------------------------------------------------*;

**	Control settings.	**;

/*%LET		Obs			=	1000;*/
%LET		Obs			=	MAX;

*--------------------------------------------------------------------------------------------------*;

PROC	TEMPLATE;
	DELETE	Base.Freq.CrossTabFreqs;
RUN;
&NoMacs		%Error_Check(ErrLvl=NOWARN);

*--------------------------------------------------------------------------------------------------*;

PROC	TEMPLATE;
/*	DEFINE	CROSSTABS	Base.Freq.CrossTabFreqs;*/
	EDIT	Base.Freq.CrossTabFreqs;
		CELLVALUE Frequency Expected Deviation CellChiSquare TotalPercent
                Percent RowPercent ColPercent CumColPercent;
		HEADER	TableOf	ControllingFor;

		DEFINE	TableOf;
			TEXT	"Tables of "	_row_label_	" by "	_col_label_
					/	_row_label_	^=	' '		OR		_col_label_	^=	' '
					;
			TEXT	"Tables of "	_row_name_	" by "	_col_name_
					;
		END;

		DEFINE	HEADER	rowsheader;
			TEXT	_row_label_	/	_row_label_	^=	' ';
			TEXT	_row_name_;
		END; 

		DEFINE	HEADER	colsheader;
			TEXT	_col_label_	/	_col_label_	^=	' ';
			TEXT	_col_name_;
		END;

		cols_header=colsheader;
		rows_header=rowsheader;
		HEADER	TableOf;

	END;
RUN;
&NoMacs		%Error_Check(ErrLvl=NOWARN);

*--------------------------------------------------------------------------------------------------*;

**  Obtain the current date and time  **;
%LET  Current_Date  =	%SYSFUNC( DATE(), YYMMDD10.);
%LET  Current_Time	=	%SYSFUNC( TIME(), TIME8.);
TITLE		"Using:  'EDIT Base.Freq.CrossTabFreqs'";
FOOTNOTE	"Run date = &Current_Date..  Run time = &Current_Time..  Observations Processed = &Obs..";

PROC	FREQ	DATA=sashelp.class		(OBS=&Obs);
	LABEL		sex	=	"Sex of Student";
	LABEL		age	=	"Age of Student";
	TABLES		sex	*	age
				/	NOROW	NOCOL
				;
RUN;
&NoMacs		%Error_Check(ErrLvl=NOWARN);

*--------------------------------------------------------------------------------------------------*;

PROC	TEMPLATE;
	DEFINE	CROSSTABS	Base.Freq.CrossTabFreqs;
/*	EDIT	Base.Freq.CrossTabFreqs;*/
		CELLVALUE Frequency Expected Deviation CellChiSquare TotalPercent
                Percent RowPercent ColPercent CumColPercent;
		HEADER	TableOf	ControllingFor;

		DEFINE	TableOf;
			TEXT	"Tables of "	_row_label_	" by "	_col_label_
					/	_row_label_	^=	' '		OR		_col_label_	^=	' '
					;
			TEXT	"Tables of "	_row_name_	" by "	_col_name_
					;
		END;

		DEFINE	HEADER	rowsheader;
			TEXT	_row_label_	/	_row_label_	^=	' ';
			TEXT	_row_name_;
		END; 

		DEFINE	HEADER	colsheader;
			TEXT	_col_label_	/	_col_label_	^=	' ';
			TEXT	_col_name_;
		END;

		cols_header=colsheader;
		rows_header=rowsheader;
		HEADER	TableOf;

	END;
RUN;
&NoMacs		%Error_Check(ErrLvl=NOWARN);

*--------------------------------------------------------------------------------------------------*;

**  Obtain the current date and time  **;
%LET  Current_Date  =	%SYSFUNC( DATE(), YYMMDD10.);
%LET  Current_Time	=	%SYSFUNC( TIME(), TIME8.);
TITLE		"Using:  'DEFINE CROSSTABS Base.Freq.CrossTabFreqs'";
FOOTNOTE	"Run date = &Current_Date..  Run time = &Current_Time..  Observations Processed = &Obs..";

PROC	FREQ	DATA=sashelp.class		(OBS=&Obs);
	LABEL		sex	=	"Sex of Student";
	LABEL		age	=	"Age of Student";
	TABLES		sex	*	age
				/	NOROW	NOCOL
				;
RUN;
&NoMacs		%Error_Check(ErrLvl=NOWARN);

*--------------------------------------------------------------------------------------------------*;

PROC	TEMPLATE;
	DELETE	Base.Freq.CrossTabFreqs;
RUN;
&NoMacs		%Error_Check(ErrLvl=NOWARN);

*--------------------------------------------------------------------------------------------------*;

&NoMacs		%Time_Stamp_SEG;

Re: Proc TEMPLATE crashes Proc FREQ?

Cynthia_sas — Sun, 29 Oct 2017 13:11:08 GMT

Hi: I'll have to check the doc later, when I'm on a different connection. Does the TEXT statement support the OR??

cynthia

Re: Proc TEMPLATE crashes Proc FREQ?

jimbarbour — Mon, 30 Oct 2017 06:10:18 GMT

@Cynthia_sas
The OR does not seem to be supported, or at least it doesn't work the way I have coded it. No error is generated, but neither does it work.

Jim