sasproc-report

PROC REPORT : Is it possible to don't print a specific row?


I am writing a report to Excel with PROC REPORT. the first column is grouped, and I add a break line before some values of it. This break line contains the value of the column if it match some conditions.

Eg.

My table contains this rows :

nom_var        |  val1       |   val2    | val3     |
_____________________________________________________
Identification |      .      |   .       |   .      |
Name           | Ou. Dj.     |   .       |   .      |
date B.        | 00/01/31    |   .       |   .      |
NAS            | 1122334     |   .       |   .      |
Revenues       |      .      |  .        |   .      |
               | R1 1250  $  | R2 1000 $ |   .      |
_____________________________________________________

In the report I have :

_____________________________________________________
                    Identification
_____________________________________________________
Identification |      .      |   .       |   .      |
Name           | Ou. Dj.     |   .       |   .      |
date B.        | 00/01/31    |   .       |   .      |
NAS            | 1122334     |   .       |   .      |
____________________________________________________
                      Revenues
_____________________________________________________
Revenues       |      .      |  .        |   .      |
               | R1 1250  $  | R2 1000 $ |   .      |
_____________________________________________________

Please, how can I revove the lines containing "Identification" and "Revenues" in the first column "nom_var"?

I mean :

Identification |      .      |   .       |   .      |

and

Revenues       |      .      |   .       |   .      |

Here is my code :

ods listing close;

*options générales;
options topmargin=1in bottommargin=1in
        leftmargin=0.25in rightmargin=0.25in
;

%let fi=%sysfunc(cat(%sysfunc(compress(&nom)),_portrait_new.xls));

ods tagsets.ExcelXP path="&cheminEx." file="&fi" style=seaside
options(autofit_height="yes"
        pagebreaks="yes"
        orientation="portrait"
        papersize="letter"
        sheet_interval="none"
        sheet_name="Infos Contribuable"
        WIDTH_POINTS = "12" WIDTH_FUDGE = ".0625" /* absolute_column_width est en pixels*/
        absolute_column_width="120,180,160,150"
        );

ods escapechar="^";

*rapport1;
/*contribuable*/
proc report data=&lib..portrait nowindows missing spanrows noheader
    style(report)=[frame=box rules=all
                    foreground=black Font_face='Times New Roman' font_size=10pt
                    background=none]
    style(column)=[Font_face='Times New Roman' font_size=10pt just=left]

;
    /*entête du tableau est la première variable de la table ==> à gauche du rapport */
    define nom_var / group order=data style(column)=[verticalalign=middle
                                        background=#e0e0e0  /* gris  */
                                        foreground=blue
                                        fontweight=bold
                                        ];
    /* Contenu */
    define valeur_var1 / style(column)=[verticalalign=top];
    define valeur_var2 / style(column)=[verticalalign=top];
    define valeur_var3 / style(column)=[verticalalign=top];

    compute before nom_var / style=[verticalalign=middle background=#e0e0e0
                                        foreground=blue fontweight=bold font_size=12pt];
        length rg $ 50;
        if nom_var in ("Identification","Actifs", "Revenus") then do;
            rg= nom_var;
            len=50;
        end;
            else do;
                rg="";
                len=0;
            end;
        line rg $varying50. len;
    endcomp ;

    title j=center height=12pt 'Portrait du contribuable';

run;

ods tagsets.ExcelXP close;
ods listing;

Solution

  • You have a artificial data construct that is not in a categorical form appropriate to the task of outputting your informative line.

    This sample shows how a DATA Step can tweak the data so you have a mySection variable that organizes the rows introduced by the nom_var row of interest (Identification and Revenues)

    The new arrangement of data is more suited for the task you are undertaking.

    data have;
    length nom_var val1 val2 val3 $50;
    infile cards dlm='|';
    input 
    nom_var           val1          val2       val3     ;
    datalines;
    Identification |      .      |   .       |   .      |
    Name           | Ou. Dj.     |   .       |   .      |
    date B.        | 00/01/31    |   .       |   .      |
    NAS            | 1122334     |   .       |   .      |
    Revenues       |      .      |  .        |   .      |
                   | R1 1250  $  | R2 1000 $ |   .      |
    run;
    

    Tweak original data so there is a categorical mySection

    data need;
      set have;
      retain mySection;
      select (nom_var);
        when ('Identification') mySection = nom_var;
        when ('Revenues') mySection = nom_var;
        otherwise OUTPUT;   * NOTE: Explicit OUTPUT means there is no implicit OUTPUT, which means the rows that do mySection= are not output;
      end;
    run;
    

    Use the new variable (mySection) for grouping (compute before), but keep it's column hidden (noprint)

    proc report data=need;
      column mySection nom_var val1 val2 val3;
      define mySection / group noprint;
      compute before mySection;
        line mySection $50.;
      endcomp;
    run;