topic Re: better code to create report table in SAS Programming

better code to create report table

Ronein — Thu, 10 Dec 2020 06:50:10 GMT

Hello,

What is the way to create this report and shorted code and more useful way?

Also in last row (the grand total) it is better that two "total" values will me merged into one "total" cover two fields ("branch" and "status")

Thanks


Data Rawtbl;
input ID branch status Y;
cards;
1 729 1 10
2 729 0 20
3 729 0 30
4 729 0 15
5 836 1 25
6 836 1 50
7 836 1 20
8 836 1 40
9 729 0 40
10 729 0 50
11 836 0 30
12 836 0 80
;
run;


PROC SQL;
	create table t1  as
	select branch,status,
		count(*) as Nr_customers  ,
	       sum(Y) as SUM_Y,
			calculated  SUM_Y/calculated Nr_customers AS AVG_Y
	from  Rawtbl
	group by branch,status
;
QUIT;


PROC SQL;
	create table t2  as
	select status,
		   count(*) as Nr_customers  ,
	       sum(Y) as SUM_Y,
			calculated  SUM_Y/calculated Nr_customers AS AVG_Y
	from  Rawtbl
	group by status
;
QUIT;


PROC SQL;
	create table t3  as
	select  count(*) as Nr_customers  ,
	       sum(Y) as SUM_Y,
			calculated  SUM_Y/calculated Nr_customers AS AVG_Y
	from  Rawtbl
;
QUIT;

Data wanted;
retain branch_char status_char;
set t1 t2 t3;
branch_char=STRIP(put(branch,best12.));
status_char=STRIP(put(status,best12.));

IF branch_char=: '.' then branch_char='Total';
IF status_char=: '.' then status_char='Total';

drop branch status;
rename branch_char=branch  status_char=status;
run;

proc report data=wanted nowd;
column branch status Nr_customers SUM_Y ;
Define branch/display;
Define status/display;
Define Nr_customers/display;
Define SUM_Y/display;
Run;

Re: better code to create report table

Shmuel — Thu, 10 Dec 2020 07:43:57 GMT

Use next code instead the 3 sql steps.

I leave to you to create the report from its output:

proc summary data=rawtbl;
   class branch status;
   var y;
   output out=sums sum=SUM_Y mean=AVG_Y;
run;

There are more options using PROC SUMMARY or PROC MEANS.

Check the documentation.

Pay attention to next columns: _TYPE_ _FREQ_.

Re: better code to create report table

andreas_lds — Thu, 10 Dec 2020 08:01:47 GMT

I don't see AVG_Y in the output at all. Why are you calculating a variable that you don't use afterwards?

proc summary data=work.rawtbl;
   class branch status;
   var y;
   output out=work.calc(rename=(_freq_=Nr_customers)) sum=sum_y;
run;

proc sort data=work.calc out=work.sorted;
   where _type_ in (0, 1, 3);
   by descending _type_;
run;

proc format;
   value SumLabel
      . = 'Total'
   ;
run;

proc print data=work.sorted;
   format branch status SumLabel.;
run;

Re: better code to create report table

Ronein — Thu, 10 Dec 2020 12:08:33 GMT

Thanks!
What is the way to have one total in grand total raw ?
Now you can see that in last raw it is written 2 times total.
Thanks

Re: better code to create report table

andreas_lds — Fri, 11 Dec 2020 06:26:26 GMT

@Ronein wrote:
Thanks!
What is the way to have one total in grand total raw ?
Now you can see that in last raw it is written 2 times total.
Thanks

Unfortunately collapsing both cells is not possible. You could replace the format by another data-step - such a step is in the code you have posted. Changing the missing-option to display missing numerics as blank, could help getting what you want:

options missing=' ';

data work.want;
   set work.sorted(rename=(branch = numBranch status = numStatus));
   
   length Branch $ 12 Status $ 1;
   
   Branch = left(put(numBranch, 12.));
   Status = put(numStatus, 1.);
   
   if missing(Status) and not missing(Branch) then Status = 'Total';
   if missing(Branch) then Branch = 'Total';
   
   drop num: _type_;
run;


proc print data=work.want;
   var branch status Nr_Customers sum_y;
run;

options missing='.';