topic Re: Column header getting suppressed when no data in ODS and Base Reporting

Column header getting suppressed when no data

deleted_user — Tue, 11 Dec 2007 11:30:25 GMT

I am creating CSV file through the below piece of code
ods csv file="c:\test\test.csv";
proc print data=WORK.QUERY7095;
run;
ods csv close;
When there is no data the csv file is created but without column header.
How should i include column header.
Any option or any other way to carete csv which will give me column header when no data is there in my report.

Re: Column header getting suppressed when no data

David_SAS — Tue, 11 Dec 2007 14:30:49 GMT

ODS is designed not to render "orphaned" headers if no data is present. You will not be able to do what you want with ODS.

-- David Kelley, SAS

Re: Column header getting suppressed when no data

deleted_user — Wed, 12 Dec 2007 09:02:58 GMT

it seems such a frequent request, that I'm surprised it doesn't appear on the SASware Ballot 2008 (see http://support.sas.com/sasware)

Perhaps there is more interest now than during the 2005 when the idea ranked 16th overall ("add an option to print a special report when no observations are found in the data set" ).

PeterC

Re: Column header getting suppressed when no data

Cynthia_sas — Wed, 12 Dec 2007 17:59:35 GMT

Hi:
But you could use a SAS Macro program to get "around" the issue. There's even a DATA Step method that uses only a few macro variables to get around the issue.
[pre]
** run a bad query and set data set name;

%let mydsn = work.myquery;

proc sql;
create table &mydsn as
select * from sashelp.class
where sex = 'm' ;
run;
quit;

** now open the data set, get the number of obs, ;
** and close the query data set.;
%let dsid=%sysfunc(open(&mydsn));
%let nobs=%sysfunc(attrn(&dsid,nobs));
%let sysrc=%sysfunc(close(&dsid));

%put -----> dsid= &dsid nobs=&nobs close sysrc=&sysrc;
%put -----> Your query ran and created &nobs observations;

ods csv file="c:\temp\want_hdr_anyway.csv";

data _null_;
if &nobs = 0 then do;
file print ods;
put _ods_;
end;
set &mydsn;
run;

proc print data=&mydsn noobs;
run;
ods csv close;
[/pre]

If you run this and the query is bad, the DATA _NULL_ step writes out the headers and one row of missing data because the PDV is built and so the PUT _ODS_ executes BEFORE the set statement tries to read the 0 obs file. Then the PROC PRINT just fails because there are 0 obs -- but the outcome is that you have a CSV file with just the headers.

There's a lot that could be done further to "macroize" the program. But without doing much more than this, someone could get headers even if the query failed.

IF you try the reverse (create a good query) like this (only change these 2 statements):
[pre]
** change the LET for the dsn;
%let mydsn = work.OKquery;

** change the WHERE statement to generate a good query;
where sex = 'M' ;

*** may also want to change the FILE= name if you want to compare the 2 files;
[/pre]

And then the DATA _NULL_ will execute, but the IF statement will fail, because &NOBS will be greater than 0. So, no harm, no foul from the DATA step -- nothing gets written to ODS. The PROC PRINT will execute and the CSV file will have headers and data.

cynthia