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;
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;