BookmarkSubscribeRSS Feed
jimbarbour
Meteorite | Level 14

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;



11 REPLIES 11
ballardw
Super User

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?
jimbarbour
Meteorite | Level 14

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

  

 

 

Frequency of Bankruptcy Year by Account Condition
Bankruptcy Year Account Condition
67 69 Total
2009
5
0.01
12
0.01
17
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.

 

jimbarbour
Meteorite | Level 14

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
ballardw
Super User

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.

jimbarbour
Meteorite | Level 14

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.

 

Sex Age
11 12 13 14 15 16 Total
F
1
5.26
2
10.53
2
10.53
2
10.53
2
10.53
0
0.00
9
47.37
M
1
5.26
3
15.79
1
5.26
2
10.53
2
10.53
1
5.26
10
52.63
Total
2
10.53
5
26.32
3
15.79
4
21.05
4
21.05
1
5.26
19
100.00

 

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

 

 


 
Frequency: Sex of Student by Age of Student
Sex of Student Age of Student
11 12 13 14 15 16 Total
F
1
5.26
2
10.53
2
10.53
2
10.53
2
10.53
0
0.00
9
47.37
M
1
5.26
3
15.79
1
5.26
2
10.53
2
10.53
1
5.26
10
52.63
Total
2
10.53
5
26.32
3
15.79
4
21.05
4
21.05
1
5.26
19
100.00

 

Run date = 2017-10-27. Run time = 16:10:54. Observations Processed = MAX.
jimbarbour
Meteorite | Level 14

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

 

Frequency
Percent
Table of Sex by Age
Sex Age
11 12 13 14 15 16 Total
F
1
5.26
2
10.53
2
10.53
2
10.53
2
10.53
0
0.00
9
47.37
M
1
5.26
3
15.79
1
5.26
2
10.53
2
10.53
1
5.26
10
52.63
Total
2
10.53
5
26.32
3
15.79
4
21.05
4
21.05
1
5.26
19
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

 

Frequency
Percent
Table of Sex by Age
Sex(Sex of
Student)
Age(Age of Student)
11 12 13 14 15 16 Total
F
1
5.26
2
10.53
2
10.53
2
10.53
2
10.53
0
0.00
9
47.37
M
1
5.26
3
15.79
1
5.26
2
10.53
2
10.53
1
5.26
10
52.63
Total
2
10.53
5
26.32
3
15.79
4
21.05
4
21.05
1
5.26
19
100.00

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

 


Run without labels and with Template overrides

 
Sex Age
11 12 13 14 15 16 Total
F
1
5.26
2
10.53
2
10.53
2
10.53
2
10.53
0
0.00
9
47.37
M
1
5.26
3
15.79
1
5.26
2
10.53
2
10.53
1
5.26
10
52.63
Total
2
10.53
5
26.32
3
15.79
4
21.05
4
21.05
1
5.26
19
100.00

 

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

 

 


Run with labels and with Template overrides

 
Frequency: Sex of Student by Age of Student
Sex of Student Age of Student
11 12 13 14 15 16 Total
F
1
5.26
2
10.53
2
10.53
2
10.53
2
10.53
0
0.00
9
47.37
M
1
5.26
3
15.79
1
5.26
2
10.53
2
10.53
1
5.26
10
52.63
Total
2
10.53
5
26.32
3
15.79
4
21.05
4
21.05
1
5.26
19
100.00

 

Run date = 2017-10-27. Run time = 17:05:27. Observations Processed = MAX.
Cynthia_sas
SAS Super FREQ

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

jimbarbour
Meteorite | Level 14

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

jimbarbour
Meteorite | Level 14

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'

Frequency
Percent
Table of Sex by Age
Sex of Student Age of Student
11 12 13 14 15 16 Total
F
1
5.26
2
10.53
2
10.53
2
10.53
2
10.53
0
0.00
9
47.37
M
1
5.26
3
15.79
1
5.26
2
10.53
2
10.53
1
5.26
10
52.63
Total
2
10.53
5
26.32
3
15.79
4
21.05
4
21.05
1
5.26
19
100.00

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

 


Using: 'DEFINE CROSSTABS Base.Freq.CrossTabFreqs'

 
Tables of Sex of Student by Age of Student
Sex of Student Age of Student
11 12 13 14 15 16 Total
F
1
5.26
2
10.53
2
10.53
2
10.53
2
10.53
0
0.00
9
47.37
M
1
5.26
3
15.79
1
5.26
2
10.53
2
10.53
1
5.26
10
52.63
Total
2
10.53
5
26.32
3
15.79
4
21.05
4
21.05
1
5.26
19
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;




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

cynthia
jimbarbour
Meteorite | Level 14
@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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 11 replies
  • 1512 views
  • 0 likes
  • 3 in conversation