topic Re: Trying to create groupings from computed variables in ODS and Base Reporting

Trying to create groupings from computed variables

vincent_s_jones — Fri, 08 Jan 2021 20:29:11 GMT

I've searched through the knowledge base, web and message boards and I could not find a similar example, although I know it exist somewhere.

So, I have a small data set

data salaries;
   input team $  type $ salary bonus;
   datalines;
Ben OFF 150000 5000
Tom OFF 200000 2000
Jerry OFF 140000 1500
Al DEF 80000 1000
Bill DEF 90000 2000
;
	run;
quit;

and I'm trying to group and calculate the salary + bonus (salary_plus).

proc report data=salaries;
	column type team salary salary_plus bonus;
	define type / group 'Type';
	define team / display;
	define bonus / analysis format=dollar32.2 sum;
	define salary / analysis format=dollar32.2 sum;
	define salary_plus / computed noprint ;
	
	compute salary_plus;
		salary_plus= bonus+salary;
	endcomp;

	break after type / summarize
		style=[font_weight=bold background=lightgray];

	compute after type ;
			line 'Total ' salary_plus dollar7.2;
	endcomp;


run;
quit;

I'm getting the correct report structure, but at the very end for the Total, I'm getting a null value. I'd like for it to say

$200,000 for DEF and $498,500 for OFF

Re: Trying to create groupings from computed variables

Cynthia_sas — Fri, 08 Jan 2021 20:54:58 GMT

Hi:
Salary and Bonus are both analysis variables in your DEFINE statement. Do you have any messages in the SAS log? You should be seeing something like this:
NOTE: Variable xxxx is uninitialized.

There should be a note for SALARY and another note for BONUS. What is in your Log?
Cynthia

Here's an example that should give you an idea about what you need to learn about PROC REPORT and how to reference variables in a COMPUTE block:

Re: Trying to create groupings from computed variables

novinosrin — Fri, 08 Jan 2021 21:21:40 GMT

Very elegant clarification @Cynthia_sas Just reading proc report book by author Jane Eslinger . I wonder whether salaryplus or Line is even needed-


data salaries;
   input team $  type $ salary bonus;
   datalines;
Ben OFF 150000 5000
Tom OFF 200000 2000
Jerry OFF 140000 1500
Al DEF 80000 1000
Bill DEF 90000 2000
;
	run;

proc report data=salaries;
	column type team salary  bonus;
	define type / order 'Type';
	define team / display;
	define bonus / analysis format=dollar32.2 sum;
	define salary / analysis format=dollar32.2 sum;
	
	break after type / summarize
		style=[font_weight=bold background=lightgray];

	compute after type ;
			Type='Total ';
           salary.sum=bonus.sum+salary.sum;
		   bonus.sum=.;
	endcomp;
run;

Type	team	salary	bonus
DEF	Al	$80,000.00	$1,000.00
	Bill	$90,000.00	$2,000.00
Total		$173,000.00	.
OFF	Ben	$150,000.00	$5,000.00
	Tom	$200,000.00	$2,000.00
	Jerry	$140,000.00	$1,500.00
Total		$498,500.00	.

Re: Trying to create groupings from computed variables

vincent_s_jones — Fri, 08 Jan 2021 21:52:37 GMT

I updated the code, and we're getting the log below and still null values in the report. The log is below.

proc report data=salaries;
	column type team salary salary_saving bonus;
	define type / group 'Type';
	define team / display;
	define bonus / analysis format=dollar32.2 ;
	define salary / analysis format=dollar32.2 ;
	define salary_saving / computed noprint ;
	
	compute salary_saving;
		salary_saving = bonus.sum+salary.sum;
	endcomp;

	break after type / summarize
		style=[font_weight=bold background=lightgray];

	compute after type ;
			line 'Total ' salary_saving ;
	endcomp;


run;
quit;

NOTE: Groups are not created because the usage of team is DISPLAY. To avoid this note, change all GROUP variables to ORDER 
variables.
NOTE: Missing values were generated as a result of performing an operation on missing values.
      Each place is given by: (Number of times) at (Line):(Column).
      7 at 1:27   
NOTE: There were 5 observations read from the data set WORK.SALARIES.
NOTE: PROCEDURE REPORT used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds

Re: Trying to create groupings from computed variables

vincent_s_jones — Fri, 08 Jan 2021 21:56:36 GMT

Just wanted to point out that we're trying to get the results

and it should read $200,000

and $498,500.

Re: Trying to create groupings from computed variables

novinosrin — Fri, 08 Jan 2021 22:14:41 GMT

Hello @vincent_s_jones Can you review the below-


data salaries;
   input team $  type $ salary bonus;
   datalines;
Ben OFF 150000 5000
Tom OFF 200000 2000
Jerry OFF 140000 1500
Al DEF 80000 1000
Bill DEF 90000 2000
;
	run;

proc report data=salaries;
	column type team salary  bonus total;
	define type / order 'Type';
	define team / display;
	define bonus / analysis format=dollar32.2 sum;
	define salary / analysis format=dollar32.2 sum;
	define total/computed noprint  ;
	break after type / summarize
		style=[font_weight=bold background=lightgray];

	compute after type ;
	  total=salary.sum+bonus.sum;
	  line 'Total' total dollar32.2;
	endcomp;
run;

Type	team	salary	bonus
DEF	Al	$80,000.00	$1,000.00
	Bill	$90,000.00	$2,000.00
DEF		$170,000.00	$3,000.00
Total $173,000.00
OFF	Ben	$150,000.00	$5,000.00
	Tom	$200,000.00	$2,000.00
	Jerry	$140,000.00	$1,500.00
OFF		$490,000.00	$8,500.00
Total $498,500.00

Re: Trying to create groupings from computed variables

vincent_s_jones — Fri, 08 Jan 2021 22:16:38 GMT

Yes, and success! I will review my code to see where I went off the course. Thanks so much!

Re: Trying to create groupings from computed variables

novinosrin — Fri, 08 Jan 2021 23:57:49 GMT

@vincent_s_jones I just checked your code, and have corrected yours too-


proc report data=salaries;
	column type team salary salary_saving bonus;
	define type / group 'Type';
	define team / display;
	define bonus / analysis format=dollar32.2 ;
	define salary / analysis format=dollar32.2 ;
	define salary_saving / computed noprint ;
	
	compute bonus;
		salary_saving = bonus.sum+salary.sum;
	endcomp;

	break after type / summarize
		style=[font_weight=bold background=lightgray];

	compute after type ;
			line 'Total ' salary_saving ;
	endcomp;


run;

The problem is in your COMPUTE Salarysavings block. NOTE: Bonus values are not available yet. So you may have to move your salarysavings after bonus in column statement or use bonus in your compute block-

This is the culprit

compute salary_saving;
salary_saving = bonus.sum+salary.sum;
endcomp;

This is the correction:-

compute bonus;
salary_saving = bonus.sum+salary.sum;
endcomp;