topic Re: Why does CALL DEFINE not accept the column name? in ODS and Base Reporting

Why does CALL DEFINE not accept the column name?

Kurt_Bremser — Thu, 21 Oct 2021 13:55:02 GMT

According to the documentation, the first parameter to CALL DEFINE (column-id) is (among others)

a character literal (in quotation marks) that is the column name

Why is the column name not accepted in this example code:

proc format;
value myfmt 100-high="high";
run;

proc report data=sashelp.class;
column name age;
define name / display;
define age / sum;
rbreak after / summarize;
compute after;
  name = "total";
  call define ("age","format","myfmt.");
endcomp;
run;

It works with either the numerical column number (2) or the sequential column name ("_c2_").

And it works if an alias is used:

proc report data=sashelp.class;
column name age=xxx;
define name / display;
define xxx / sum;
/*compute age;
if _break_ = "_RBREAK_" then call define (_col_,"format","3.");
endcomp;*/
rbreak after / summarize;
compute after;
  name = "total";
  call define ("xxx","format","myfmt.");
endcomp;
run;

So what is the difference between the column name itself and the alias?

Re: Why does CALL DEFINE not accept the column name?

data_null__ — Thu, 21 Oct 2021 14:43:55 GMT

The name is AGE.SUM for an analysis variable.

call define ("age.sum","format","myfmt.");

Re: Why does CALL DEFINE not accept the column name?

Kurt_Bremser — Mon, 25 Oct 2021 08:15:35 GMT

Makes sense. But why does the alias work without .sum? Is the alias in fact applied to the statistic, and not the variable as such?

Re: Why does CALL DEFINE not accept the column name?

Cynthia_sas — Mon, 25 Oct 2021 13:40:47 GMT

Hi:
It is one of the referencing rules of PROC REPORT. In a COMPUTE block GROUP, ORDER and DISPLAY items can use the simple column name reference. ANALYSIS items must use the referencing form of variable.statistic. An analysis item that uses an alias can use the simple alias name. An exception to this rule would be any item under an ACROSS usage variable. An ANALYSIS item or an ALIAS item under an ACROSS item would need to use the absolute column number in a COMPUTE block.
Cynthia

Re: Why does CALL DEFINE not accept the column name?

Kurt_Bremser — Mon, 25 Oct 2021 17:16:12 GMT

That somehow led me to the relevant piece of documentation that describes it..

Thank you to all!

Re: Why does CALL DEFINE not accept the column name?

JackHamilton — Tue, 09 Nov 2021 22:11:11 GMT

Is there a way to find the absolute column numbers of columns without preprocessing the data?

Just to make up a simple example of when that might be useful, let's suppose I have some id columns, and then across columns for a variable, then a summary column, and I want to make the last across column pink. If I knew the column number of the final summary column, I could find the column number of the last across column by subtracting 1.

I have occasionally had reason to run a PROC SUMMARY on data, and then PROC REPORT on the summary output. It surprised me that sometimes with large data sets the SUMMARY+REPORT approach was noticeably faster than creating the same final report using PROC REPORT alone. I would have thought the run times would be very similar because the same calculation engine is used in both.

Re: Why does CALL DEFINE not accept the column name?

PaigeMiller — Tue, 09 Nov 2021 22:14:14 GMT

@JackHamilton wrote:
Is there a way to find the absolute column numbers of columns without preprocessing the data?

Just to make up a simple example of when that might be useful, let's suppose I have some id columns, and then across columns for a variable, then a summary column, and I want to make the last across column pink. If I knew the column number of the final summary column, I could find the column number of the last across column by subtracting 1.

I have occasionally had reason to run a PROC SUMMARY on data, and then PROC REPORT on the summary output. It surprised me that sometimes with large data sets the SUMMARY+REPORT approach was noticeably faster than creating the same final report using PROC REPORT alone. I would have thought the run times would be very similar because the same calculation engine is used in both.

@JackHamilton

Better to put this in its own thread, rather than here in an unrelated (and solved) thread.

Re: Why does CALL DEFINE not accept the column name?

JackHamilton — Wed, 10 Nov 2021 03:43:58 GMT

@PaigeMiller I don't understand why you think it's an unrelated question. The original question was about determining the values possible for a column reference in CALL DEFINE, and my question is also about that. And the answer to my question might provide an alternative answer to the problem behind the original question.

Re: Why does CALL DEFINE not accept the column name?

Kurt_Bremser — Wed, 10 Nov 2021 05:56:33 GMT

Your question is unrelated, but more important, my question has been solved and marked as such, so any additional post won't get much attention.

Therefore it's better for you to post your question in a new topic.

Re: Why does CALL DEFINE not accept the column name?

PaigeMiller — Wed, 10 Nov 2021 11:10:29 GMT

@JackHamilton I'm not going to debate it with you. You should see this at the top of the thread:

So help yourself, and help the Community — start a new thread.