topic Re: check table results into report in SAS Programming

check table results into report

me55 — Tue, 04 Jun 2019 18:28:08 GMT

okay, i have a pdf report i am trying to build. it is...

options orientation=landscape;
ods pdf file="RTP03.pdf";

Title 'title 01';
Footnote Generated by &_CLIENTUSERNAME with SAS Enterprise Guide &_CLIENTVERSION;

proc sql;
select ...
from TEST01;
quit;

title;
footnote;

ods pdf close;

what i want it to do is to replace the sql pull into a macros that will check if a table (TEST01) has results in it that will be added from a insert into query. if there are not results, put a line like 'there are no results' on the pdf and if the table has results, pull the data and put that on the pdf. i have a macro set but i just cant finish it off yet. i am just not knowledgeable enough in sas macros to finish this off...

%macro tblchk;

%let dsid=%sysfunc(open(TEST01));
%let nobs=%sysfunc(attrn(&dsid,nlobs));
%let dsid=%sysfunc(close(&dsid));

%if &nobs=0 %then %do;
%put There are no issues;
%end;

%else %do;
proc sql;
select *
from TEST01;
quit;
%end;

%mend tblchk;

something is just off here...i am just not sure what.

Re: check table results into report

VDD — Tue, 04 Jun 2019 18:46:01 GMT

%macro tblchk;
data _null_;
%let dsid=%sysfunc(open(TEST01));
%let nobs=%sysfunc(attrn(&dsid,nlobs));
%let dsid=%sysfunc(close(&dsid));
%if &nobs=0 %then %do;
%put There are no issues;
%end;
%else %do;
proc sql;
select *
from TEST01;
quit;
%end;
run;
%mend tblchk;

put a data null in the macro

Re: check table results into report

me55 — Tue, 04 Jun 2019 19:14:58 GMT

yeah, that is not working. i should be able to replace the above sql in the report with the macro and have it either say there are no results or pull the results and that is not happening when i simply put the data _null_; in there...

Re: check table results into report

ballardw — Tue, 04 Jun 2019 19:26:55 GMT

Instead of

%if &nobs=0 %then %do;
%put There are no issues;
%end;

Try

%if &nobs=0 %then %do;
Proc odstext;
p "There are no results";
run;
%end;

%put writes to the log

Put can write to the log or a file but you will need to a bit more to create ODS output.

Simpler is to use a Proc designed to create ODS output.

If you want to make the text prettier you can add style elements to the P statements in Proc Odstext.

Re: check table results into report

me55 — Mon, 10 Jun 2019 17:21:13 GMT

in case someone needs it, here is the final code that i settled on from the help of others and my searching for code...

options orientation=landscape;
options label nodate pageno=1;

ods escapechar='^';
ods pdf file=”RPT.pdf" startpage=no;


Title height=14pt underlin=1 'Reporting - Report';
Footnote Generated by &_CLIENTUSERNAME with SAS Enterprise Guide &_CLIENTVERSION;

ods pdf text = "^{style [just=center font=(Arial) fontsize=12pt fontweight=bold 
		color=black] Sub-Title}";

%macro tblchk;
data _null_;
%let dsid=%sysfunc(open(TABLE01));
%let nobs=%sysfunc(attrn(&dsid,nlobs));
%let dsid=%sysfunc(close(&dsid));

%if &nobs=0 %then %do;
proc odstext;
p 'There are NO Results!' / style=[color=red just=c fontsize=10pt fontweight=bold];
%end;

%else %do;
proc sql;
select *
from TABLE01;
quit;
%end;

%mend tblchk;
run;

%tblchk;
run;

thanks for the help as usual...