BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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.
3 REPLIES 3
David_SAS
SAS Employee
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
deleted_user
Not applicable
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
Cynthia_sas
SAS Super FREQ
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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 643 views
  • 0 likes
  • 3 in conversation