Compute a sum and label it in proc report

[_Manhattan]

Hello there,

I am trying to compute an additional row that contains a label and the sum of certain columns. My Output looks like this:

As you can see the labeling and computing works fine for the second table (Variable ATBG10A), but not for my other tables (ACXG48...). I guess the problem is that the "ACXG48..." variables are numeric. The Log shows the following hints:

NOTE: Character values have been converted to numeric values at the places given by: (Line):(Column). 1:12

NOTE: Format ACXG48A was not found or could not be loaded.

NOTE: Invalid numeric data, 'Gesamt' , at Zeile 1 Spalte 12.

Do you have any guidance on how to change the format of the ACXG48... variables? Or is my approch the wrong one anyway? My code looks like this and I have also attached the data and the code:

/*** Libname path ***/
%let dpath = yourpath; /* Change path */
Libname d "&dpath";


/* Macro Variable for Sorting */
proc sql noprint;
  select distinct Sort into :Sort separated by " "
  from d.Excelbeispiel2;
quit;

/*** Macro for PDF ***/
ods _all_ close;
ods escapechar="^";
%macro SkaTest (nvariables=);
  %do i = 1 %to &nvariables;
    %let SortID = %scan(&Sort., &i.);
	ods html close;

/* Use Info1 Column to iterate over Itemnames */
   %local Info1Items; 
   proc sql noprint; 
      select distinct Info1 into :Info1Items separated by " "
      from d.ExcelBeispiel2
      where Sort = &SortID.;
   quit; 
   
/* Data for Iteration Cycles */
   data d.QuellenBeispiel;
    	set d.ExcelBeispiel2;
    	where Sort = &SortID.;
    	drop Part;
   run;

ods pdf startpage=never;
ods layout gridded x=1cm columns=1;

/* Print variable Information */ 	
    ods region;
    proc report data=d.QuellenBeispiel noheader;
    	column Info1 Info2 Bsort;
    	define Info1 / display;
    	define Info2 / display;
    	define Bsort / noprint;
    	compute Info1;
    		Info1 = tranwrd(Info1,"Kodierung","^{style[fontweight=bold]Kodierung}");
    	endcomp;
    	compute Bsort;
    		if Bsort = "*" then do;
    		call define (_ROW_,"style","style={font_weight=bold fontsize=10pt}");
    		end;
    		if Bsort ="~" then do;
    		call define (_row_, "style", "style={backgroundcolor=lightgrey fontweight=bold}");
    		end;
    	endcomp;
    run;
    
ods layout end;

/* Macro to match variable information with frequency tables */
ods layout gridded x=1cm columns=2;
%macro PrintItem(Item=);

	%if %sysfunc(exist(d.F&Item.)) %then %do;
	ods region;
      proc report data=d.F&Item.;
        column &Item. n pct pct_se;
        define &Item. / display "&Item" style(header)=[backgroundcolor=lightgrey];
        define n / "Absolut" style(header)=[backgroundcolor=lightgrey];
        define pct / "Prozent" width=12 format=6.2 style(header)=[backgroundcolor=lightgrey];
        define pct_se / display "(SE)" format=6.2 style(header)=[backgroundcolor=lightgrey];
        rbreak after / summarize
        	style(summary) = {font_weight=bold};
        compute after ;
        	&Item. = "Gesamt";
        endcomp;
      run;
    %end;
    
    %if %sysfunc(exist(d.M&Item.)) %then %do;
	ods region;
      proc report data=d.M&Item.;
        column dvar mnx mnx_se sdx Min Max n; 
        define dvar / display "Variable" style(header)=[backgroundcolor=lightgrey];
        define mnx / "M" format=6.2 style(header)=[backgroundcolor=lightgrey];
        define mnx_se / display "(SE)" format=6.2 style(header)=[backgroundcolor=lightgrey];
        define sdx / display "SD" format=6.2 style(header)=[backgroundcolor=lightgrey];
        define Min / display "Min" format=6.0 style(column) = {just = center} style(header)=[backgroundcolor=lightgrey];
        define Max / display "Max" format=6.0 style(column) = {just = center} style(header)=[backgroundcolor=lightgrey];
        define n / display "n" style(header)=[backgroundcolor=lightgrey];
        rbreak after / summarize
        	style(summary) = {font_weight=bold};
        compute after ;
        	dvar = "Gesamt";
        endcomp;
      run;
	%end; 

%mend PrintItem;
 
   %local j currItem; 
   %let j=1; 
   %let currItem=%scan(&Info1Items.,&j.,%str( )); 
   
   %do %while(%length(&currItem.)>0); 
  
      %PrintItem(Item=&currItem.); 
      %let j=%eval(&j.+1); 
      %let currItem=%scan(&Info1Items.,&j.,%str( )); 
      
   %end; 
   
ods layout end;   
  %end;
  
%mend SkaTest;

/* Print PDF */
options papersize=a4 orientation=portrait leftmargin=0.5cm rightmargin=0.5cm topmargin=0.5cm bottommargin=0.5cm nodate nonumber pdfprint=HRES;
ods listing close;
ods pdf file="yourpath\TestPDF2.pdf";
%SkaTest(nvariables = 2);
ods pdf close;

Thank you!

[Tom]

So that variable is NUMERIC.  You cannot store "TOTAL"  (or "Gesamt") into a numeric variable, you need a character variable.

It also has a format attached to it that you did not provide.  We can test anyway by setting NOFMTERR system option.

A simple way to fix that is to convert it to a character variable.  You might be able to do it in PROC REPORT, but it is probably easier to do it in a data step.

%let item=ACXG48A;
options nofmterr ;
libname d 'c:\downloads'; 
data for_report;
  set d.f&item(rename=(&item=_&item));
  length &item $30;
  &item=vvalue(_&item);
run;

proc report data=for_report;
  column &Item. n pct pct_se;
  define &Item. / display "&Item" style(header)=[backgroundcolor=lightgrey];
  define n / "Absolut" style(header)=[backgroundcolor=lightgrey];
  define pct / "Prozent" width=12 format=6.2 style(header)=[backgroundcolor=lightgrey];
  define pct_se / display "(SE)" format=6.2 style(header)=[backgroundcolor=lightgrey];
  rbreak after / summarize
    style(summary) = {font_weight=bold};
  compute after ;
    &Item. = "Gesamt";
  endcomp;
run;

Result:

[PaigeMiller]

A drawback turning a numeric variable to a character variable for purposes of reporting, is that character variables are sorted alphabetically, not numerically. So if you had three numeric values, such as 3, 6, 10, then converting these to characters and having them sort alphabetically means you get 10, 3, 6 in order, and this is not what most people want. 

I would leave the variable as numeric, and have a format to place text into the report. Example:

proc format;
    value cylf .='All';
run;
proc report data=sashelp.cars;
    columns cylinders cyl1 msrp;
    define cylinders/group noprint;
    define cyl1/computed format=cylf. 'Cylinders';
    define msrp/mean;
    compute cyl1;
        cyl1=cylinders;
    endcomp;
    rbreak after/summarize style=[font_style=italic];
run;

[Cynthia_sas · Accepted solution]

Hi:

  Here's a solution example with PROC REPORT:

  In this example, the character version of the main numeric variable is created in PROC REPORT as a COMPUTED item and so the numeric variable is still used for ordering, but the character version allows you to put your custom text on the break line.

Cynthia

[_Manhattan]

Thank you so much Cynthia! I have integrated that approach easily in my code 🙂