topic Re: computed variables under across variables in ODS and Base Reporting

computed variables under across variables

js5 — Wed, 21 Oct 2020 14:12:48 GMT

Dear community,

I am trying to collapse median, q1 and q3 for an analysis variable to have them displayed in one cell in the final report. It works as long as I use only group variables, as soon as I switch to across variables I get an error:

ERROR: The variable type of 'MSRP.MEDIAN'n is invalid in this context.

So this works:

proc report data=sashelp.cars;
	columns Origin Make Type msrp msrp=msrpq1 msrp=msrpq3 stats;
	define Origin /" " group;
	define Make /" " group;
	define Type / " " group;

	define msrp / analysis median noprint;
	define msrpq1 / analysis q1 noprint;
	define msrpq3 / analysis q3 noprint;
	define stats / computed;

	compute stats / character length=50;
		stats = catt(put(msrp.median, dollar7.1-l), " (", put(msrpq1, dollar7.1-l), "-", put(msrpq3, dollar7.1-l), ")");
	endcomp;
run;

But this does not:

proc report data=sashelp.cars;
	columns Origin Make Type msrp msrp=msrpq1 msrp=msrpq3 stats;
	define Origin /" " group;
	define Make /" " group;
	define Type / " " group;

	define msrp / analysis median noprint;
	define msrpq1 / analysis q1 noprint;
	define msrpq3 / analysis q3 noprint;
	define stats / computed;

	compute stats / character length=50;
		stats = catt(put(msrp.median, dollar7.1-l), " (", put(msrpq1, dollar7.1-l), "-", put(msrpq3, dollar7.1-l), ")");
	endcomp;
run;

Ultimately I would like to obtain a table similar to one generated by below code, but with median, q1 and q3 collapsed into one computed variable:

proc report data=sashelp.cars;
	columns Origin Make Type, msrp, (n median q1 q3);
	define Origin /" " group;
	define Make /" " group;
	define Type / " " across;
	define n / format=2.;
run;

What am I missing? Thanks!

Re: computed variables under across variables

PaigeMiller — Wed, 21 Oct 2020 14:29:46 GMT

@js5 wrote:

But this does not:

proc report data=sashelp.cars;
	columns Origin Make Type msrp msrp=msrpq1 msrp=msrpq3 stats;
	define Origin /" " group;
	define Make /" " group;
	define Type / " " group;

	define msrp / analysis median noprint;
	define msrpq1 / analysis q1 noprint;
	define msrpq3 / analysis q3 noprint;
	define stats / computed;

	compute stats / character length=50;
		stats = catt(put(msrp.median, dollar7.1-l), " (", put(msrpq1, dollar7.1-l), "-", put(msrpq3, dollar7.1-l), ")");
	endcomp;
run;

Works for me. What errors are you seeing from this code?

Re: computed variables under across variables

js5 — Wed, 21 Oct 2020 14:34:12 GMT

Apologies, I made a copy-paste error. This is the failing code:

proc report data=sashelp.cars;
	columns Origin Make Type, msrp msrp=msrpq1 msrp=msrpq3 stats;
	define Origin /" " group;
	define Make /" " group;
	define Type / " " across;

	define msrp / analysis median noprint;
	define msrpq1 / analysis q1 noprint;
	define msrpq3 / analysis q3 noprint;
	define stats / computed;

	compute stats / character length=50;
		stats = catt(put(msrp.median, dollar7.1-l), " (", put(msrpq1, dollar7.1-l), "-", put(msrpq3, dollar7.1-l), ")");
	endcomp;
run;

I.e. with Type as across variable.

Re: computed variables under across variables

ballardw — Wed, 21 Oct 2020 14:35:45 GMT

@js5 wrote:

Dear community,

I am trying to collapse median, q1 and q3 for an analysis variable to have them displayed in one cell in the final report. It works as long as I use only group variables, as soon as I switch to across variables I get an error:

ERROR: The variable type of 'MSRP.MEDIAN'n is invalid in this context.

So this works:
proc report data=sashelp.cars;
	columns Origin Make Type msrp msrp=msrpq1 msrp=msrpq3 stats;
	define Origin /" " group;
	define Make /" " group;
	define Type / " " group;

	define msrp / analysis median noprint;
	define msrpq1 / analysis q1 noprint;
	define msrpq3 / analysis q3 noprint;
	define stats / computed;

	compute stats / character length=50;
		stats = catt(put(msrp.median, dollar7.1-l), " (", put(msrpq1, dollar7.1-l), "-", put(msrpq3, dollar7.1-l), ")");
	endcomp;
run;
But this does not:
proc report data=sashelp.cars;
	columns Origin Make Type msrp msrp=msrpq1 msrp=msrpq3 stats;
	define Origin /" " group;
	define Make /" " group;
	define Type / " " group;

	define msrp / analysis median noprint;
	define msrpq1 / analysis q1 noprint;
	define msrpq3 / analysis q3 noprint;
	define stats / computed;

	compute stats / character length=50;
		stats = catt(put(msrp.median, dollar7.1-l), " (", put(msrpq1, dollar7.1-l), "-", put(msrpq3, dollar7.1-l), ")");
	endcomp;
run;
Ultimately I would like to obtain a table similar to one generated by below code, but with median, q1 and q3 collapsed into one computed variable:
proc report data=sashelp.cars;
	columns Origin Make Type, msrp, (n median q1 q3);
	define Origin /" " group;
	define Make /" " group;
	define Type / " " across;
	define n / format=2.;
run;
What am I missing? Thanks!

Please double check your posted code. Your second block of code does not contain any ACROSS variables. If I change the Type to across I do not get that error, though the output is likely not what you want either. So, as with any error: Copy from the log the entire procedure code and any messages and then paste that to the forum in a code box opened with the </> icon to preserve formatting.

Personally I would likely pre-calculate the string using proc summary followed by a data step and then just have the group,across variables and that string value.

Re: computed variables under across variables

PaigeMiller — Wed, 21 Oct 2020 14:49:24 GMT

This would be pretty simple if you wanted a column for the MEDIAN, another column for the Q1 and another column for the Q3.

If you absolutely must have a text string rather than the actual numeric values (why?), then I think you need some help from @Cynthia_sas

Re: computed variables under across variablesiables

data_null__ — Wed, 21 Oct 2020 15:02:40 GMT

You need a different variable nested under across. I'm not sure why you're using ACROSS if all the variables under are NOPRINT. This fixed the context issue.

proc report data=sashelp.cars /*list showall*/;
	columns Origin Make Type, (msrp) msrp=msrp0 msrp=msrpq1 msrp=msrpq3 stats;
	define Origin /" " group;
	define Make /" " group;
	define Type / " " across;
   define msrp    / n noprint; *<===;
	define msrp0   / analysis median noprint;
	define msrpq1 / analysis q1 noprint;
	define msrpq3 / analysis q3 noprint;
	define stats  / computed;

	compute stats / character length=50;
		stats = catt(put(msrp0, dollar7.1-l), " (", put(msrpq1, dollar7.1-l), "-", put(msrpq3, dollar7.1-l), ")");
	endcomp;
run;

Re: computed variables under across variables

ballardw — Wed, 21 Oct 2020 15:29:22 GMT

You may want to reference this document https://support.sas.com/resources/papers/proceedings15/SAS1642-2015.pdf

Because of the way ACROSS creates columns you have to reference the specific column number for calculations. Which I believe means that you have to test for which column the type is in and then compute with separate columns. I think.

Like I mentioned, likely much easier to make this variable BEFORE Proc Report and then just display it.

Re: computed variables under across variables

Cynthia_sas — Wed, 21 Oct 2020 18:55:59 GMT

Hi: Thanks for the vote of confidence @PaigeMiller. This paper https://support.sas.com/resources/papers/proceedings14/SAS388-2014.pdf goes into detail on using ACROSS items. And using a subset of SASHELP.CARS, if I understand what the original poster wants, I'd use THIS approach shown below:

The way to find out what the correct absolute column numbers are is shown in my user group paper.

Cynthia

Re: computed variables under across variables

js5 — Thu, 22 Oct 2020 06:55:42 GMT

Thank you for the responses! @data_null__'s solution does run, but type is not shown as across variable. The report has 3 columns: Origin, Make and Stats.

@Cynthia_sas's solution does indeed produce the intended result, with the disadvantage of having to refer to values by column numbers instead of variable names. Reading the paper, it appears there is no way around it, correct? In this case I might do what @PaigeMiller said and pre-calculate the concatenated column as the actual data I need to display has considerably more across groups.

Re: computed variables under across variables

Cynthia_sas — Thu, 22 Oct 2020 19:36:33 GMT

Hi:
You MUST refer to the items under an ACROSS item by the absolute column number in a COMPUTE block. There's no way around this except to "pre-calculate" the values you want with PROC MEANS or PROC TABULATE. Even PROC REPORT would pre-summarize for you but for this purpose, I think PROC MEANS is the way to go:

Cynthia

Re: computed variables under across variables

js5 — Fri, 23 Oct 2020 06:15:54 GMT

This is exactly what I did, except that instead of using a dummy variable, I defined string as a group variable. According to https://blogs.sas.com/content/sgf/2014/03/14/how-to-avoid-6-common-proc-report-errors/ it is a valid solution too.

Re: computed variables under across variables

Cynthia_sas — Fri, 23 Oct 2020 14:56:24 GMT

Hi:
Yes, that is an alternative. The dummy variable way has been handy for me because using a dummy variable at the end of a row is also useful if I need to go back and do any conditional changes based on multiple items in the report row. Since it serves a double purpose, it's the one I generally use.
Cynthia