CODEWITHSUNDEEP
sas
D. O.
D. O.

Reputation: 626

Proc Report issue : I am getting a value selected into variable

I have a macro like:

%macro links;
PROC SQL;
CREATE TABLE T_US  AS
    SELECT DISTINCT
    ID_SECON,
    NO_BEN,
    s_an
     FROM rm.BENEF
;
QUIT; 

proc sql;
    select max(input(substr(s_an,1,4),4.)) into :max_sign
    from T_US
;
quit;
%mend links;

Then I create a report using ODS:

%macro theRep();

options topmargin=1in bottommargin=1in
        leftmargin=0.25in rightmargin=0.25in
;

ods tagsets.ExcelXP path="&cheminEx." file="&fi" style=seaside
options(autofit_height="yes"
        pagebreaks="yes"
        orientation="portrait"
        papersize="letter"
        WIDTH_POINTS = "12" WIDTH_FUDGE = ".0625"
        absolute_column_width="110,175,180,200"
        );

ods escapechar="^";

%theODS;

ods tagsets.ExcelXP close;
ods listing;
%mend theRep;

%macro theODS;
ods tagsets.ExcelXP 
options(sheet_interval="none"
        sheet_name="thename"
        );

proc report data=T_US nowindows spanrows missing 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]
;

    col ID_SECON NO_BEN s_an;

    define ID_SECON / group order=data noprint;
    define NO_BEN / style(column)=[verticalalign=top];
    define s_an / style(column)=[verticalalign=top];

    compute before ID_SECON / style=[verticalalign=middle background=#f0f0f                     foreground=blue fontweight=bold font_size=12pt];
    line ID_SECON $25.;
    endcomp ;

    title j=center height=12pt 'The title';
run;

%mend theODS;

The report is showing nice. But the problem is:

The value of the variable max_sign is displayed at the end of the report even if I did not asked for it in the define part.

Please, can you help? Why the variable which is not in the dataset T_US appears in the report?

Thanks!

Upvotes: 1

Views: 74

Answers (3)

Vojta F
Vojta F

Reputation: 564

As the others pointed out, the trick is in using into :(some name) and noprint. Additionally you shall be aware that a macrovariable is a character string by nature. Why does it matter? If you store a number in a macrovariable, it is stored digit by digit exactly as you input it; it is highly advisable, thus, to format the number prior to assigning it to the macrovariable.

In other words, if the true value is 2.36581 and you format it as 2.4 and assign it to a macrovariable there is no way to get the higher precision back. All that is stored in the macrovariable is "two, fullstop, four".

Upvotes: 0

Richard
Richard

Reputation: 27498

The SQL :into clause transfers a value from a select query into one or more macro variables depending on syntax. The select output is not automatically suppressed when you use :INTO. SQL output is suppressed using the NOPRINT option, or RESET NOPRINT; statement (SQL-only).

So you will want

proc sql noprint;
  select max(input(substr(s_an,1,4),4.)) into :max_sign
  from T_US
;

The :INTO clause also has a TRIMMED option that is helpful when transferred values are used in a title or footnote and the 'default' transfer has leading or trailing spaces.

into :max_sign TRIMMED

Upvotes: 3

mjsqu
mjsqu

Reputation: 5417

It's possible the value of max_sign is being displayed because of this step:

proc sql;
    select max(input(substr(s_an,1,4),4.)) into :max_sign
    from T_US
;
quit;

Not the PROC REPORT. Try adding the NOPRINT option to suppress output from PROC SQL:

proc sql NOPRINT;
    select max(input(substr(s_an,1,4),4.)) into :max_sign
    from T_US
;
quit;

Upvotes: 1

Related Questions