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;
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
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 |
|
|
|
||||||
2010 |
|
|
|
||||||
2011 |
|
|
|
||||||
2012 |
|
|
|
||||||
2013 |
|
|
|
||||||
2014 |
|
|
|
||||||
2015 |
|
|
|
||||||
2016 |
|
|
|
||||||
2017 |
|
|
|
||||||
Total |
|
|
|
The really wild thing is that even if I comment out the WHERE clause, I still get the WHERE clause error message.
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 |
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.
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 |
|
|
|
|
|
|
|
||||||||||||||
M |
|
|
|
|
|
|
|
||||||||||||||
Total |
|
|
|
|
|
|
|
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 |
|
|
|
|
|
|
|
||||||||||||||
M |
|
|
|
|
|
|
|
||||||||||||||
Total |
|
|
|
|
|
|
|
Run date = 2017-10-27. Run time = 16:10:54. Observations Processed = MAX. |
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 |
|
|
Run date = 2017-10-27. Run time = 17:05:27. Observations Processed = MAX. |
Run with labels and without Template overrides |
|
|
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 |
|
|
|
|
|
|
|
||||||||||||||
M |
|
|
|
|
|
|
|
||||||||||||||
Total |
|
|
|
|
|
|
|
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 |
|
|
|
|
|
|
|
||||||||||||||
M |
|
|
|
|
|
|
|
||||||||||||||
Total |
|
|
|
|
|
|
|
Run date = 2017-10-27. Run time = 17:05:27. Observations Processed = MAX. |
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
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
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' |
|
|
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 |
|
|
|
|
|
|
|
||||||||||||||
M |
|
|
|
|
|
|
|
||||||||||||||
Total |
|
|
|
|
|
|
|
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.